Time period spanning midnight

[Stephen White]

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?

 

[FloMM2]

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

 

[T. Valko]

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]

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?

 

[Stephen White]

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?

 

[Stephen White]

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

Why not look in Excel help to see what the MOD function does?

 

[Stephen White]

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.

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