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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )