How do I calculate Time difference from PM to AM

[Guest]

When calculating Time (H:mm:ss) using formula (B1-a1+(b1<A1))*24 how do I get
the answer in H:MM:SS

EX: A1 = 22:54:33
B1 = 3:22:54 Using above formula I get 4.4725

 

[Niek Otten]

=B1-A1+(B1<A1)

Format as time (copy the format from A1)
If you need the time to calculate (to multiply with an hourly rate), use *24
like in your first formula.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Guest]

THnaks Nick but I am having problems with Formatting I get the answer but I
want to see it in H:mm:Ss of elasped time - The Answer should be eg. 4:28:54;
when I try the custom format I get 11:20:24 which is the time no the elasped
time.

Hope you have an answer - Its driving me crazy

Regards
Jas

 

[Niek Otten]

I get 4:28:21

I think you multiply by 24 and still try to format as time. That will not
work correctly. Don't multiply, unless you want the number of hours to
calculate with and in that case, don't try to format as time, because for
Excel it isn't time. Time is a fraction of a day, and a day is 1.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel