Results 1 to 7 of 7

20120503, 15:30 #1
 Join Date
 Dec 2007
 Posts
 179
 Thanks
 3
 Thanked 0 Times in 0 Posts
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

20120503, 15:45 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,495
 Thanks
 33
 Thanked 63 Times in 59 Posts
Try: =TEXT(B1A1,"h:mm")
with A1 being 12:45 PM and B1 being 1:00 PM
or: =MINUTE(B1A1)
or, if the difference is in excess of 60 minutes: =(B1A1)*1440Last edited by kweaver; 20120503 at 15:51.

20120503, 16:00 #3
 Join Date
 Dec 2007
 Posts
 179
 Thanks
 3
 Thanked 0 Times in 0 Posts
None of them seem to work.

20120503, 16:40 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,048
 Thanks
 145
 Thanked 543 Times in 518 Posts
Hi
kweaver is correct, but..
in cell [B1], for 1:00pm, you need to enter this in 24hr 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

20120504, 03:53 #5
 Join Date
 Dec 2007
 Posts
 179
 Thanks
 3
 Thanked 0 Times in 0 Posts
>> in cell [B1], for 1:00pm, you need to enter this in 24hr clock format i.e. as 13:00.
OK, if I do that then the following solution works:
TEXT(B1A1,"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

20120504, 04:59 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,048
 Thanks
 145
 Thanked 543 Times in 518 Posts
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 '12hour' 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.5A1,B1A1),"h:mm")
You can use a custom format of h:mm if you don't want to see am/pm.
see attached file
zeddy

The Following User Says Thank You to zeddy For This Useful Post:
amakeler (20120506)

20120506, 14:53 #7
 Join Date
 Dec 2007
 Posts
 179
 Thanks
 3
 Thanked 0 Times in 0 Posts
Yes! That's it!
Thanks!
avraham