Duration time

[CHML]

Hi. I am trying to get the duration time, but I am getting a bunch of number signs. Can someone kindy assist? Thanks

 

[JamesCanale]

I'm betting that if you put 13:00 into F, it would work.

It's probably taking 1:00 as 1:00am, and subtracting gives you a negative number. Formatting the negative number as a date/time is going to be problematic.

 

[CHML]

Was also thinking but I would want time format to stay like this;

 

[Scott Huish]

=MOD(F2-E2,1)
Format as h:mm

If you want it as a decimal number
=MOD(F2-E2,1)*24
Format in the number format you want.

 

[SunnyAlv]

Try this

=TEXT(F2,"h:mm")-TEXT(F2,"h:mm")

 

[felixstraube]

No matter the format you still need the end time to be greater that the start time for the subtraction to give a positive number. Excel only can show positive times, or you get the ####.
So keep the format with AM / PM but you will have to haven ending time as 1:00 PM. not 1:00 AM.

Start	End	End - Start
9:00 AM​	1:00 PM​	04:00:00​

Or is the 1:00 AM from the next day? If thats so you will have to add the date to the calculation.

 

[Tupe77]

Which one do you mean in row 2 as Col A and Col B have dates?
Duration between: 9:00 to 1:00
OR
Duration between 9/25/2023 9:00 to 9/29/2023 1:00

 

[JamesCanale]

If you want to keep the formatting, you'll have to do:

=IF(F2<E2,0.5,0)+F2-E2

 

[CHML]

@felixstraube, You're right about the AM/PM. It was my mistake.

Thank you all for coming forward with your assistance.