Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
how do we calculate the amount of time between two set times? for example,
d2 as time of 10pm and b2 has time of 7am. do we need to have the column formatted as a time or number? Or is there a specific calc we need? Help please |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
=MOD(D2-B2,1), and format as [h]:mm
The MOD() formula allows for times running over midnight, so if you wanted to work out the time from 10pm to 7am, =MOD(B2-D2,1) would do it for you. If you want to convert to decimal hours, =24*MOD(B2-D2,1), and format as number or general, not time. You'll have to make sure you enter 7 am with the space, as 7am without a space will be treated as text. Similarly with the pm. -- David Biddulph "tam25" wrote in message ... how do we calculate the amount of time between two set times? for example, d2 as time of 10pm and b2 has time of 7am. do we need to have the column formatted as a time or number? Or is there a specific calc we need? Help please |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi,
You don't say which is the earlier time so the difference could be 3 hrs or 21 hrs:) This formula will work it out the difference with the earlier time in B2 =IF(B2D2,D2+1-B2,D2-B2) Use a custom format of hh:mm Mike "tam25" wrote: how do we calculate the amount of time between two set times? for example, d2 as time of 10pm and b2 has time of 7am. do we need to have the column formatted as a time or number? Or is there a specific calc we need? Help please |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
9 or 15, not 3 or 21, Mike?
-- David Biddulph "Mike H" wrote in message ... Hi, You don't say which is the earlier time so the difference could be 3 hrs or 21 hrs:) This formula will work it out the difference with the earlier time in B2 =IF(B2D2,D2+1-B2,D2-B2) Use a custom format of hh:mm Mike "tam25" wrote: how do we calculate the amount of time between two set times? for example, d2 as time of 10pm and b2 has time of 7am. do we need to have the column formatted as a time or number? Or is there a specific calc we need? Help please |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
it's early :)
"David Biddulph" wrote: 9 or 15, not 3 or 21, Mike? -- David Biddulph "Mike H" wrote in message ... Hi, You don't say which is the earlier time so the difference could be 3 hrs or 21 hrs:) This formula will work it out the difference with the earlier time in B2 =IF(B2D2,D2+1-B2,D2-B2) Use a custom format of hh:mm Mike "tam25" wrote: how do we calculate the amount of time between two set times? for example, d2 as time of 10pm and b2 has time of 7am. do we need to have the column formatted as a time or number? Or is there a specific calc we need? Help please |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Calculating time difference then rounding down | Excel Discussion (Misc queries) | |||
Calculating time difference in minutes | Excel Worksheet Functions | |||
Calculating Time difference based on a condition | Excel Discussion (Misc queries) | |||
Calculating time difference over midnight! | Excel Discussion (Misc queries) | |||
Calculating time difference | Excel Discussion (Misc queries) |