I am trying to set up a roster/time sheet that will give me my expected gross
wages for the week. I am having trouble because staff are finishing at 1am or
2am and i'm not sure how to calculate the hours worked using a formula. I am
just getting #### because it is a negative number. eg 17:00pm - 1.00am.
Thank you in advance

HI Jules
First your number should be real time formatted as HH:MM, your showing 17:00
pm. 17:00 should'nt have any pm.
A1 has starting time 17:00
B1 has 02:00
C1 =MOD(B1-A1,1)Format Custom[h]:mm
HTH
John
"Jules" wrote in message
...
I am trying to set up a roster/time sheet that will give me my expected
gross
wages for the week. I am having trouble because staff are finishing at 1am
or
2am and i'm not sure how to calculate the hours worked using a formula. I
am
just getting #### because it is a negative number. eg 17:00pm - 1.00am.
Thank you in advance

hi
xl keep time as a percent of a day.
12 hours is .5 day.
8 hours is .33333 day.
you are subtracting 1 am(.041667 day) from 5pm(.708333 day) and getting
-6667 day which is negative and since xl wont display negative time, you get
#######.
what you need to do is.....
subtract 5pm(.708333 day) from one then add 1am(.041667 day) which will yield
7 hour(.291667 day) + 1hour(.041667 day) which equals .33333 day or 8 hours.
5pm in c11 ,1am in c12.....change to suit......
use this fomula......
=IF(C12-C11<0,(1-C11)+C12,(C12-C11))

yeah i know but i didn't invent this stuff. i just learned how to play games
with it.

Regards
FSt1

"Jules" wrote:

I am trying to set up a roster/time sheet that will give me my expected gross
wages for the week. I am having trouble because staff are finishing at 1am or
2am and i'm not sure how to calculate the hours worked using a formula. I am
just getting #### because it is a negative number. eg 17:00pm - 1.00am.
Thank you in advance

=MOD(C12-C11,1)
may be easier than
=IF(C12-C11<0,(1-C11)+C12,(C12-C11))
--
David Biddulph

"FSt1" wrote in message
...
hi
xl keep time as a percent of a day.
12 hours is .5 day.
8 hours is .33333 day.
you are subtracting 1 am(.041667 day) from 5pm(.708333 day) and getting
-6667 day which is negative and since xl wont display negative time, you
get
#######.
what you need to do is.....
subtract 5pm(.708333 day) from one then add 1am(.041667 day) which will
yield
7 hour(.291667 day) + 1hour(.041667 day) which equals .33333 day or 8
hours.
5pm in c11 ,1am in c12.....change to suit......
use this fomula......
=IF(C12-C11<0,(1-C11)+C12,(C12-C11))

yeah i know but i didn't invent this stuff. i just learned how to play
games
with it.

Regards
FSt1

"Jules" wrote:

I am trying to set up a roster/time sheet that will give me my expected
gross
wages for the week. I am having trouble because staff are finishing at
1am or
2am and i'm not sure how to calculate the hours worked using a formula. I
am
just getting #### because it is a negative number. eg 17:00pm - 1.00am.
Thank you in advance