Hi folks,

Happy New Years to everyone.

This is my first post and I am not to bad with excel but trying something new here and need advice to see if what I want is possible.

At my workplace, people receive night shift allowances if they work between 00:00 – 04:00. So for example, if they work 8pm to 5am, they will get extra money for all the hours work.

At the moment, I have a list of people who clock in and out. So in L1, I would see a persons clock in time of 19:00 and in L2, their clock out time of 05:00.

Now as you can see, this person is working between 00:00 and 04:00. How would I go about putting a formula together giving me any sort of indication that he would be eligible to receive a night shift payment.

Sorry if this is confusing at all but please let me know if you require any further information.

Thanks
Pri

Hi. Is the data always presented in this way? I.e. the start time and end time for any given worker at always in two rows next to each other?

Hi,

That is correct

If I can suggest, look into the way Excel handles dates. Briefly, Excel treats each day as an integer number, and a time as a fraction of a day. If you type the formula =NOW() into a cell you'll get a date and timestamp: as I write this the date and time is 1/3/2013 11:09:46 AM, for instance. If you then format that cell to show a number, Excel's numeric equivalent is 41277.4651157407. The 41277 part refers to January 3; the decimal fraction is the elapsed portion of the day and gives the time. In your spreadsheet you may find it helpful to convert the date in one cell and the punch in / punch out time in another cell to these Excel equivalents. This will allow you to calculate the time worked, and whether the person qualifies for shift differential. It will also allow you to determine whether or not the person qualifies for shift differential.

Do the date and time already appear in your spreadsheet in Excel's date format, or is it text? You can test for this by going to another cell and entering
=K2 + 5
If K2 is in date format you'll see a date that's five days later; if it's text it will return an error. No worries if it's text, though, you can still convert text to the date format.

Is the additional payment only for the hours worked between midnight and 4am or for the whole shift?

If the worker only crosses into this 4 hours period for say half an hour, at either end, does the whole shift accrue the extra payment?

Also, are you looking at a one off extra payment if the shift crosses into this 4 hour period or an additional hourly amount?

Sorry for all the additional questions but the original post was a little vague on details.

Hi Spencer,

Not a problem at all.

The additional payment is for the whole shift as long as a individual has covered 12-4am. So for example, if they worked 8pm - 4am, they would receive an extra £1.50 per hour for 8 hours.

If the individual only works couple of hours within that window, they would not receive a payment. They must have worked 12-4. If they work 8pm - 12.30am, they would not receive any night shift allowance. Even if they work, say 11pm-7am, because they 4 hour window is being worked, they would get night shift allowance for the whole 8 hours.

I hope this helps but please let me know if you require any more.

Thanks

Hi Prets

Have a look at the attached file. if I understand you correctly, as long as their shift includes the 4hrs 00:00-04:00, they get the night shift rate!

So in column G of the attached sheet is a IF function returning "Extra Payment" if they worked those hours, using the small table in L1:N3.
I have also included a column for payment using a nominal hr/rate (J2).

You might have to adjust the formula, as I might have misunderstood your post. I have paid them the shift plus an extra 4 hrs at £1.50 ph. This can be adjusted in the formula to pay an extra 1.5 for each hour if required.

Hopefully this is of some use!

Hi Kevin,

Thats brilliant. I cant actually express how helpful that is. I had to tweek it a little so the additional £1.50 is paid for all the hours but that wasn't difficult.

Again, thank you so much.

Pri

Hi Prets

No problem, glad I could be of some assistance and thank you for the feed back.

Kevin