Specific hours worked between two time intervals

[Prets]

Hi folks,

Happy New Years to everyone.

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

At my workplace, people receive night shift allowances if they wor
between 00:00 – 04:00. So for example, if they work 8pm to 5am, the
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,
would see a persons clock in time of 19:00 and in L2, their clock ou
time of 05:00.

Now as you can see, this person is working between 00:00 and 04:00. Ho
would I go about putting a formula together giving me any sort o
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 requir
any further information.

Thanks
Pr

+-------------------------------------------------------------------
|Filename: example.jpg
|Download: http://www.excelbanter.com/attachment.php?attachmentid=720
+-------------------------------------------------------------------

 

[Spencer101]

Hi folks,

Happy New Years to everyone.

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

At my workplace, people receive night shift allowances if they wor
between 00:00 – 04:00. So for example, if they work 8pm to 5am, the
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,
would see a persons clock in time of 19:00 and in L2, their clock ou
time of 05:00.

Now as you can see, this person is working between 00:00 and 04:00. Ho
would I go about putting a formula together giving me any sort o
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 requir
any further information.

Thanks
Pri

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Prets]

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

Hi,

That is correc

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Dave O]

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: asI 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 personqualifies 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 stillconvert text to the date format.

 

[Kevin@Radstock]

Hi Prets

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

So in column G of the attached sheet is a IF function returning "Extr
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 you
post. I have paid them the shift plus an extra 4 hrs at £1.50 ph. Thi
can be adjusted in the formula to pay an extra 1.5 for each hour i
required.

Hopefully this is of some use!

Hi Spencer,

Not a problem at all.

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

If the individual only works couple of hours within that window, the
would not receive a payment. They must have worked 12-4. If they wor
8pm - 12.30am, they would not receive any night shift allowance. Even i
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.

Thank

+-------------------------------------------------------------------
|Filename: 001-Specific hours worked between two time intervals.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=722
+-------------------------------------------------------------------

 

[Prets]

Hi Prets

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

So in column G of the attached sheet is a IF function returning "Extr
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 you
post. I have paid them the shift plus an extra 4 hrs at £1.50 ph. Thi
can be adjusted in the formula to pay an extra 1.5 for each hour i
required.

Hopefully this is of some use!

Hi Kevin,

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

Again, thank you so much.

Pr

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Kevin@Radstock]

Hi Prets

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

Kevin

Hi Kevin,

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

Again, thank you so much.

Pr

+-------------------------------------------------------------------
+-------------------------------------------------------------------