I am creating a timesheet for our 24 hour operation and I have run into
difficulty when the work shift crosses over midnight. The formula works
perfect when the shift starts at 7:00 am but when the shifts start at 7:00 pm
and crosses over midnight it shows -12 hours worked for the shift.

Shifts are always changing so the same person can be on nights and days
during the same pay period. Do I need to include the date in this
calculation?

Thanks for your help.

Try this...

A1 = start time = 7:00 PM
B1 = end time = 7:00 AM

=MOD(B1-A1,1)

Formatted as h:mm retruns 12:00

=MOD(B1-A1,1)*24

Formatted as General or Number returns 12

--
Biff
Microsoft Excel MVP


"dspohn" wrote in message
...
I am creating a timesheet for our 24 hour operation and I have run into
difficulty when the work shift crosses over midnight. The formula works
perfect when the shift starts at 7:00 am but when the shifts start at 7:00
pm
and crosses over midnight it shows -12 hours worked for the shift.

Shifts are always changing so the same person can be on nights and days
during the same pay period. Do I need to include the date in this
calculation?

Thanks for your help.

That works great. I was making it way to complicated. Thank you

"T. Valko" wrote:

Try this...

A1 = start time = 7:00 PM
B1 = end time = 7:00 AM

=MOD(B1-A1,1)

Formatted as h:mm retruns 12:00

=MOD(B1-A1,1)*24

Formatted as General or Number returns 12

--
Biff
Microsoft Excel MVP


"dspohn" wrote in message
...
I am creating a timesheet for our 24 hour operation and I have run into
difficulty when the work shift crosses over midnight. The formula works
perfect when the shift starts at 7:00 am but when the shifts start at 7:00
pm
and crosses over midnight it shows -12 hours worked for the shift.

Shifts are always changing so the same person can be on nights and days
during the same pay period. Do I need to include the date in this
calculation?

Thanks for your help.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"dspohn" wrote in message
...
That works great. I was making it way to complicated. Thank you

"T. Valko" wrote:

Try this...

A1 = start time = 7:00 PM
B1 = end time = 7:00 AM

=MOD(B1-A1,1)

Formatted as h:mm retruns 12:00

=MOD(B1-A1,1)*24

Formatted as General or Number returns 12

--
Biff
Microsoft Excel MVP


"dspohn" wrote in message
...
I am creating a timesheet for our 24 hour operation and I have run into
difficulty when the work shift crosses over midnight. The formula works
perfect when the shift starts at 7:00 am but when the shifts start at
7:00
pm
and crosses over midnight it shows -12 hours worked for the shift.

Shifts are always changing so the same person can be on nights and days
during the same pay period. Do I need to include the date in this
calculation?

Thanks for your help.

Hi,

=abs(ending time-beg time)*12

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"dspohn" wrote in message
...
I am creating a timesheet for our 24 hour operation and I have run into
difficulty when the work shift crosses over midnight. The formula works
perfect when the shift starts at 7:00 am but when the shifts start at 7:00
pm
and crosses over midnight it shows -12 hours worked for the shift.

Shifts are always changing so the same person can be on nights and days
during the same pay period. Do I need to include the date in this
calculation?

Thanks for your help.

Thats works too. Thanks for the help

"dspohn" wrote:

I am creating a timesheet for our 24 hour operation and I have run into
difficulty when the work shift crosses over midnight. The formula works
perfect when the shift starts at 7:00 am but when the shifts start at 7:00 pm
and crosses over midnight it shows -12 hours worked for the shift.

Shifts are always changing so the same person can be on nights and days
during the same pay period. Do I need to include the date in this
calculation?

Thanks for your help.