Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time period spanning midnight
I am working on a time sheet. Suppose in cell A1 I have a start time and in cell B1 a finish time in a 24hr format (say for example 23:00 or just 2300) and suppose that the time between them spans midnight. So the two values might be A1 21:30 B1 3:45 What is the neatest formula to calculate the number of hours between them? -- Stephen White ) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time period spanning midnight
Stephen,
You might want to consider this: Column A - Date, Column B -Time, Column C - In, Column D - out, Column F - Total Hours In F2 formula is "=IF(B1="","",24*(A2-A1)+24*(B2-B1)) hth "Stephen White" wrote: I am working on a time sheet. Suppose in cell A1 I have a start time and in cell B1 a finish time in a 24hr format (say for example 23:00 or just 2300) and suppose that the time between them spans midnight. So the two values might be A1 21:30 B1 3:45 What is the neatest formula to calculate the number of hours between them? -- Stephen White ) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time period spanning midnight
Enter the times as TIMES
A1 = 21:30 B1 = 3:45 What is the neatest formula to calculate the number of hours between them? =MOD(B1-A1,1) -- Biff Microsoft Excel MVP "Stephen White" wrote in message ... I am working on a time sheet. Suppose in cell A1 I have a start time and in cell B1 a finish time in a 24hr format (say for example 23:00 or just 2300) and suppose that the time between them spans midnight. So the two values might be A1 21:30 B1 3:45 What is the neatest formula to calculate the number of hours between them? -- Stephen White ) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time period spanning midnight
Many thanks for your suggestion, but I don't follow it. Could you give me a set of example values for cells A1 - D2 and F1 and F2 so I can get a better idea of your thinking? In article , FloMM2 writes Stephen, You might want to consider this: Column A - Date, Column B -Time, Column C - In, Column D - out, Column F - Total Hours In F2 formula is "=IF(B1="","",24*(A2-A1)+24*(B2-B1)) hth "Stephen White" wrote: I am working on a time sheet. Suppose in cell A1 I have a start time and in cell B1 a finish time in a 24hr format (say for example 23:00 or just 2300) and suppose that the time between them spans midnight. So the two values might be A1 21:30 B1 3:45 What is the neatest formula to calculate the number of hours between them? -- Stephen White ) -- Stephen White ) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time period spanning midnight
Many thanks. This is very neat and works so far as I have tested it so long as I make it =MOD(B1-A1,1)*24 But can you please explain to me why it works? Why does it not always give a result of zero since the modulus of anything divided by 1 must be zero, musn't it and anything multiplied by zero is zero? In article , T. Valko writes Enter the times as TIMES A1 = 21:30 B1 = 3:45 What is the neatest formula to calculate the number of hours between them? =MOD(B1-A1,1) -- Stephen White ) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time period spanning midnight
Why not look in Excel help to see what the MOD function does?
-- David Biddulph "Stephen White" wrote in message ... Many thanks. This is very neat and works so far as I have tested it so long as I make it =MOD(B1-A1,1)*24 But can you please explain to me why it works? Why does it not always give a result of zero since the modulus of anything divided by 1 must be zero, musn't it and anything multiplied by zero is zero? In article , T. Valko writes Enter the times as TIMES A1 = 21:30 B1 = 3:45 What is the neatest formula to calculate the number of hours between them? =MOD(B1-A1,1) -- Stephen White ) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time period spanning midnight
David, I shall completely understand if you do not want to be bothered to explain and ignore this further message of mine but I did look at Excel help where I find "MOD(number,divisor) Returns the remainder after number is divided by divisor. The result has the same sign as divisor." Hence my puzzlement and my inquiry. Stephen In article , David Biddulph <groups@[at] writes Why not look in Excel help to see what the MOD function does? -- David Biddulph "Stephen White" wrote in message ... Many thanks. This is very neat and works so far as I have tested it so long as I make it =MOD(B1-A1,1)*24 But can you please explain to me why it works? Why does it not always give a result of zero since the modulus of anything divided by 1 must be zero, musn't it and anything multiplied by zero is zero? In article , T. Valko writes Enter the times as TIMES A1 = 21:30 B1 = 3:45 What is the neatest formula to calculate the number of hours between them? =MOD(B1-A1,1) -- Stephen White ) -- Stephen White ) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time period spanning midnight
My apologies David. I think the penny is beginning to drop. I guess the answer to my question is that the values stored as times are always less than one and so there will be a remainder other than zero when these are divided by 1. Your suggestion is a very neat solution. Many thanks. In article , Stephen White writes David, I shall completely understand if you do not want to be bothered to explain and ignore this further message of mine but I did look at Excel help where I find "MOD(number,divisor) Returns the remainder after number is divided by divisor. The result has the same sign as divisor." Hence my puzzlement and my inquiry. Stephen In article , David Biddulph <groups@[at] writes Why not look in Excel help to see what the MOD function does? -- David Biddulph "Stephen White" wrote in message ... Many thanks. This is very neat and works so far as I have tested it so long as I make it =MOD(B1-A1,1)*24 But can you please explain to me why it works? Why does it not always give a result of zero since the modulus of anything divided by 1 must be zero, musn't it and anything multiplied by zero is zero? In article , T. Valko writes Enter the times as TIMES A1 = 21:30 B1 = 3:45 What is the neatest formula to calculate the number of hours between them? =MOD(B1-A1,1) -- Stephen White ) -- Stephen White ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find the difference between start time and end time when spanning. | Excel Discussion (Misc queries) | |||
Time after midnight | Excel Worksheet Functions | |||
time around midnight | Excel Worksheet Functions | |||
subtraction off time after midnight | Excel Worksheet Functions | |||
Time calculation (in hh.mm) spanning more than one day | Excel Discussion (Misc queries) |