aCan anyone help please?

I am trying to put together a spreadsheet that will keep track of my hours
worked in a month, on different days. I have 2 rates at work. On day shift
rate, and a night shift rate. sometimes I work 09:00 till 18:00 at rate one,
and sometimes 22:00 till 06:00 at rate 2. The days vary, and I can get it to
work my day shift, but not my night shift. The calculation cell needs to be
able to calculate the difference in both shifts. Thanks for the help :)

Say start time is in A1 and End time in B1 then enter the below formula
into C2 and format as [h]:mm

=IF(A1B1,B1+1-A1,B1-A1)

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=567218

Izzy Wrote:
aCan anyone help please?

I am trying to put together a spreadsheet that will keep track of my
hours
worked in a month, on different days. I have 2 rates at work. On day
shift
rate, and a night shift rate. sometimes I work 09:00 till 18:00 at
rate one,
and sometimes 22:00 till 06:00 at rate 2. The days vary, and I can get
it to
work my day shift, but not my night shift. The calculation cell needs
to be
able to calculate the difference in both shifts. Thanks for the help
:)
Hi Izzy,

If your day rate was say, £15.25
and your night rate was £20.25

you could try this

=IF(A1B1,B1+1-A1,B1-A1)*24*15.25
=IF(A2B2,B2+1-A2,B2-A2)*24*20.25

Then format C1 to currency

oldchippy :)


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907
View this thread: http://www.excelforum.com/showthread...hreadid=567218

To calculate time difference in hours either

=B1-A1+(A1B1)

or

=MOD(B1-A1,1)

if you want the hours in decimal format multiply by 24, e.g.

=MOD(B1-A1,1)*24


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=567218

Thanks for that guys. Would you know the following too......

We have 2 pay rates. 06:00 to 22:00 is rate 1 and 22:00 to 06:00 is rate 2.

I can work out the rates if I work within rate 1 or rate 2, but if I work
03:00 to 15:00 or 20:00 to 04:00 crossing over between the rates, I get hung
up. I need to be able to work out my days pay for all my shifts including the
cross overs.

Thanks :)

"Izzy" wrote:

aCan anyone help please?

I am trying to put together a spreadsheet that will keep track of my hours
worked in a month, on different days. I have 2 rates at work. On day shift
rate, and a night shift rate. sometimes I work 09:00 till 18:00 at rate one,
and sometimes 22:00 till 06:00 at rate 2. The days vary, and I can get it to
work my day shift, but not my night shift. The calculation cell needs to be
able to calculate the difference in both shifts. Thanks for the help :)

Not particularly straightforward but if your shift start time is in A2
and end time in B2, in time format then this formula in C2 will give
you rate 2 hours

=IF(MOD(A2+1/12,1)<1/3,MIN(1/3,MOD(B2+1/12,1))-MOD(A2+1/12,1),IF(MOD(B2+1/12,1)<A2,MIN(MOD(B2+1/12,1),1/3),0))

format as h:mm

this format in D2 will then give you rate 1 hours

=MOD(B2-A2,1)-C2

then to calculate total pay for shift

=(C2*rate2+D2*rate1)*24


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=567218