calculations using times
Posted by charlotte cummins on September 11, 2001 1:05 AM
How do I calculate the time elapsed for example if someone has work from 9.30 am to 4.45 pm?
Thank you
Charlotte
Posted by Eric on September 11, 2001 4:56 AM
=(HOUR(B2)+MINUTE(B2)/60)-(HOUR(A2)+MINUTE(A2)/60)
If start time is in Col(A) end time is in Col(B), and time is formatted as time (not text), then:
=(HOUR(B2)+MINUTE(B2)/60)-(HOUR(A2)+MINUTE(A2)/60)
should work.
Posted by Eric on September 11, 2001 5:03 AM
Also Jay Deitch's earlier post with the "format method", link inside
Posted by Eric on September 11, 2001 5:43 AM
above formula doesn't handle times crossing midnight
In my sample data the following formula:
=(HOUR(B2)+DAYS360(A2,B2)*24+MINUTE(B2)/60)-(HOUR(A2)+MINUTE(A2)/60)
does handle times crossing midnight, so long as the time entries also contain the date information.
I can't be sure that the "days360" function will always return a value of 1 for differences in consecutive days though.
If your start and end dates are separate entries, then the situation is greatly simplified as a simple subtraction of the two values gives their difference in days. For instance if your start date was in c2 and your end date was in d2, then you could replace the "days360(a2,b2)" portion of the equation with "(d2-c2)" so the formula would become:
=(HOUR(B2)+(d2-c2)*24+MINUTE(B2)/60)-(HOUR(A2)+MINUTE(A2)/60)
Posted by Aladin Akyurek on September 11, 2001 7:29 AM
Charlotte,
In C2 enter: =B2+(B2 < A2)-A2
where A2 contains the start time and B2 the end time.
Custom format C2 as [h]:mm.
Aladin
Posted by Andrew on September 11, 2001 8:11 AM
In Col A, just type in the dd/mm/yyyy hh:mm
and in Col B type the same (Even day difference exist).
Then in Col C type =b1-a1 (B1 MUST > A1)
and format the cell contents of Col C as [hh]:mm and it can cater for day difference
Posted by Eric on September 11, 2001 9:49 AM
Aladin, if you have the time, could you walk me through what the formula does?
I'd like to avoid my own convoluted machinations in the future!
Posted by Aladin Akyurek on September 11, 2001 12:06 PM
Re: Aladin, if you have the time, could you walk me through what the formula does?
Eric,
The best way to see how that formula computes is using F9.
Go to the formula bar, select (B2 < A2) bit and hit F9.
If B2 is smaller than A2, you'll get FALSE and see there 0 by coercion, because this expression is surrounded by arithmetic operators.
The coercion here is that Excel will attempt to add the logical value with a number. In order to do that Excel converts the logical value FALSE to 0.
In case the expression evaluates to TRUE, you'll see 1 to appear there: that is, 1 must be added to B2, a time value.
Aladin
============ ,
Posted by Eric on September 11, 2001 2:24 PM
Thx for your time (again) Aladin!
I never realized that adding one to a date moved the date by 1 day! Thanks for dragging me through that.
