Time formulas in Excel

[Izzy]

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

[VBA Noob]

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

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

VBA Noob

[oldchippy]

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(A1>B1,B1+1-A1,B1-A1)*24*15.25
=IF(A2>B2,B2+1-A2,B2-A2)*24*20.25

Then format C1 to currency

oldchippy

[daddylonglegs]

To calculate time difference in hours either

=B1-A1+(A1>B1)

or

=MOD(B1-A1,1)

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

=MOD(B1-A1,1)*24

[Izzy]

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

[daddylonglegs]

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