Finding difficult to find difference between AM and PM times

[AlexMN1920]

I have dataset with time recorded in AM/PM format. I am trying to find the duration by substracting from one from another. But it gives error when the time time overlaps to another day. 

Here is the link to the work book.

https://drive.google.com/file/d/1ygEjTenMBbHDh0furXEcLjHyWoGc_43k/view?usp=sharing

https://docs.google.com/spreadsheets/d/1bW8AaF-omVvhgnEoXwNcNcKoQh2MMKP4/edit?usp=sharing&ouid=10953...

[#2 · Accepted solution]

@AlexMN1920 

Change the formula for Duration to:

=MOD(C2-B2,1) and copy it down.

[#3]

Wow!! That's amazing. Even after spending more than 30 minutes in Google, I din't find such short solution. Thank you!!!

[#4]

Hi @Riny_van_Eekelen,

If you don't mind can you please help me to understand the logic behind it. Thank you in advance!!

[#5]

@AlexMN1920 First you need to understand that times are in fact numbers. 11PM equals 23 hours into a 24-hour day. Thus, 23/24 = 0.958333

The picture below is just an example of how that works. Look into the MS support pages for MOD function to see how that function works. I can't really describe it better myself.

On row 4 you see the calculation in a Number format. The MOD function returns a positive number 0.083333. Multiply this number by 24 and you get 2 (hours). Or just format the number 0.083333 as Time to return 02:00.

[#6]

:thumbs_up::thumbs_up::thumbs_up: