Weekly Challenges
Solve the challenge, share your solution and summit the ranks of our Community!Also available in | Français | Português | Español | 日本語
IDEAS WANTED
Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEA
Probably not the most efficient, but I got the job done. I considered a few assumptions with 0 and negative values. Details in the spoiler.
Spoiler
The first time I ran through this, I converted military to standard first, then calculated minutes. However, this produced incorrect results. So I started over and did all the calculating off military first then converted to standard time while keeping the military calculation.
To start, I inserted a colon in the middle of the text string so I'd be able to convert to time using DateTime. I then used select to convert the original times into numerical values so I could convert them into standard time using numerical calculations, and converted the new times into strings so I'd be able to perform DateTimeDiff function to calculate elapsed time. Next I used select to convert original back to string and performed the insert colon formula a second time to capture the standard time in place of the military time.
At this point, I had to make a couple of assumptions. If Elapsed = 0 then I assumed it was a 24 hour (1440 minute) elapsed time. For example, if start time is 2:00 and end time is 2:00, then I assume it's 2:00 into the following day. I know it's not a 12 hour difference because I did the elapsed time based off of Military (this is why I had to start over from the first attempt). Thus, I converted all 0s into 1440. I also assumed negative values to flow into the following day as well, but less than 24 hours. You can't take a course in negative time. So if start was 11:00 and end was 10:50, then I assume it was 10 minutes shy of 24 hours. In this calculation I did 1440 + the negative value.
To start, I inserted a colon in the middle of the text string so I'd be able to convert to time using DateTime. I then used select to convert the original times into numerical values so I could convert them into standard time using numerical calculations, and converted the new times into strings so I'd be able to perform DateTimeDiff function to calculate elapsed time. Next I used select to convert original back to string and performed the insert colon formula a second time to capture the standard time in place of the military time.
At this point, I had to make a couple of assumptions. If Elapsed = 0 then I assumed it was a 24 hour (1440 minute) elapsed time. For example, if start time is 2:00 and end time is 2:00, then I assume it's 2:00 into the following day. I know it's not a 12 hour difference because I did the elapsed time based off of Military (this is why I had to start over from the first attempt). Thus, I converted all 0s into 1440. I also assumed negative values to flow into the following day as well, but less than 24 hours. You can't take a course in negative time. So if start was 11:00 and end was 10:50, then I assume it was 10 minutes shy of 24 hours. In this calculation I did 1440 + the negative value.
