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 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


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



--
Prets

[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: 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.

[Spencer101]

Prets;1608354 Wrote:
> 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?


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



--
Spencer101

[Prets]

Spencer101;1608360 Wrote:
> 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


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



--
Prets

[Kevin@Radstock]

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!



Prets;1608369 Wrote:
> 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


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



--
Kevin@Radstock

[Prets]

Kevin@Radstock;1608384 Wrote:
> 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


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



--
Prets

[Kevin@Radstock]

Hi Prets

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

Kevin

Prets;1608390 Wrote:
> 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


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



--

Kevin@Radstock