Validating hourly rates

**city** · 01-28-2012, 04:08 AM

Hi All,

Please help me in creating a formula for this, I am not really good in creating formulas. . . =(
I apologize before hand about the way I explain things. . . I might not be clear on some areas. . . . .

I am planning to create a timekeeping spreadsheet, but I can't determine as to how am to validate the hours with night differential or hours w/o night differential.
Is there a way to generate a formula on how to get this.

Please help me. . . . . .

Night differential hours: 11PM to 6AM

See attached file for a much more clear image. .

timekeeping.xlsx

**Pete_UK** · 01-28-2012, 10:11 AM

I've put formulae in columns F and G of your table in the attached file.

Hope this helps.

Pete

**city** · 01-28-2012, 11:40 AM

WoW!! This has been haunting me! Thanks so so so much Pete_UK!

**Pete_UK** · 01-28-2012, 11:54 AM

You're welcome - thanks for feeding back.

Pete

**city** · 01-29-2012, 05:26 AM

I apologize before hand. I tagged the thread as solved but I just have a few questions regarding the formula.

Pete,

Can I just ask something. I was kinda reviewing the formula. Honestly I don't understand it.

Can I ask what "MOD(D3,1)" is for?? This is actually the first time i've seen this used.

Please educate me on this.

**Pete_UK** · 01-29-2012, 07:43 AM

MOD gives you the remainder after division, so in this case it returns the fractional part of D3. Dates are stored in Excel as the number of elapsed days since some starting point (1st Jan 1900), and times are stored as fractions of a 24-hour day, so the MOD function ensures that we are only looking at the time part of D3, ignoring any integer (day) values.

Hope this helps.

Pete

**city** · 01-31-2012, 01:07 PM

Thanks for the Information Pete_UK. I reopened the thread again because I made some review on my own, but can't figure out some scenarions

There are some scenarios that I tried in here that kinda caused some errors.. . .

Row 3: I tried changing the time to 12AM and the date to the same one as the shift end's date. It caused G3 to be negative. (no formula changes)
Row 4: I change the shift start and shift end to the same date and made some changes to forumla in g4, it did get the right result but whenever I change the date of shift start to 1/0/1900 with PM time output in G4 becomes #VALUE!

See attched file

ef20_timekeepingv2.xlsx