Get Inspire insights from former attendees in our AMA discussion thread on Inspire Buzz. ACEs and other community members are on call all week to answer!
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.
