# Thread: Need formula to calculate time duration from 12:45 to 1:00

1. ## Need formula to calculate time duration from 12:45 to 1:00

Hi all,

I need a formula to calculate time duration between 12:45 to 1:00.

The answer of course should be 0:15 i.e., 15 minutes

12:45 is 45 mins after midday and 1:00 is 1 c'clock lunch time

E.g., 12:45 is in cell A1, and 1:00 is in cell B1,
and I want the duration in cell C1 , what formula do I put in cell C1?

TIA

-avi

2. Try: =TEXT(B1-A1,"h:mm")
with A1 being 12:45 PM and B1 being 1:00 PM

or: =MINUTE(B1-A1)

or, if the difference is in excess of 60 minutes: =(B1-A1)*1440

3. None of them seem to work.

4. Hi

kweaver is correct, but..
in cell [B1], for 1:00pm, you need to enter this in 24-hr clock format i.e. as 13:00.
You can still format the cell to show 13:00 as 1:00 PM, but the contents are 13:00
see attached file

zeddy

5. >> in cell [B1], for 1:00pm, you need to enter this in 24-hr clock format i.e. as 13:00.

OK, if I do that then the following solution works:

TEXT(B1-A1,"h:mm")

The other two just display 12:00 AM.
----------

But what if I don't want the time values in A1 and B1 to be displayed with "AM" and "PM"?

I.e., which ever that the user types in the time, the calculation formula in C1 should handle it well and display the right duration.

-avraham

6. Hi

Excel stores time as a decimal number, as 'part' of a day.
6:00am = 0.25
midday = 0.5
9:00pm = 0.75 etc.

So, to work with a '12-hour' clock only, and always assuming that the time entered in [B1] is always after the time entered in cell [A1], you just need to use the following formula in cell [C1]:
=TEXT(IF(B1<A1,B1+0.5-A1,B1-A1),"h:mm")

You can use a custom format of h:mm if you don't want to see am/pm.

see attached file

zeddy

7. ## The Following User Says Thank You to zeddy For This Useful Post:

amakeler (2012-05-06)

8. Yes! That's it!

Thanks!

-avraham

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•