how to get HOURS under NIGHT DIFFERENTIAL using EXCEL?

A2 start, B2 end'

C2 =B2-A2

D2 =IF(AND(MOD(A2,1)< TIME(22,0,0),MOD(A2,1)> TIME(6,0,0)), TIME(22,0,0)-MOD(A2,1),0)

+IF(AND(MOD(B2,1)< TIME(22,0,0),MOD(B2,1)> TIME(6,0,0)),MOD(B2,1)-TIME(6,0,0),0)

E2 =C2-D2

Col C total time, col D standard hours, col E night differential hours

Format A&B as mm/dd/yyyy hh:mm, C,D,E as [h]:mm

Since you have full date/times here, a simple formula will suffice:

=B2 - A1

With only times, you have to resort to some trickery:

=B2 - A1 + (A1 > B2)

The inequality (>) checks if adjustment is needed because the times are crossing over midnight. TRUE gets converted to 1 when used in math.

Remember to format as a time or you will get a decimal value.

[add] Is there a reason this account is copying your question?

http://answers.yahoo.com/activity?show=Gc5afLOIaa

Not too hard, but requires a few formulas to figure:

I split your date & times up in separate cells, so

A1 is start date, A2 is the start time

B1 is the end date, B2 is the end time

in C1 and C2( you can put this anywhere, though) I put the start and end times of the shift differential.

in E1:

=IF(B1<C1,TEXT(C1-B1,"hh:mm"),

TEXT(0,"hh:mm"))

This gives hours worked before 10pm, if none, it returns 00:00

in E2:

=IF(B2>C2,TEXT(B2-C2,"hh:mm"),

TEXT(0,"hh:mm"))

This gives hours worked after 6am, if none, it returns 00:00

in E3:

=IF(B2-B1>0, B2-B1, 1+B2-B1)

This gives the total # of hours worked, which, for your start and end times is 9:30 (use custom format "hh:mm")

In cell E5, or wherever you want:

=E3-(E1+E2)

This gives you your shift differential hours, which, for this example, is 6:00 (again, use custom format "hh:mm")

There are probably ways to do this without splitting the date and time into separate cells, but this is easier, and you can split them easily with Text to Columns if you have a lot of entries.

Now that was challenging! I worked all of the formulas out and entered test data. My solution seperates straight time from night diff hours. It won't fit very well here, if you give me your e-mail i can send it to you. I'm nwest4000 on yahoo messenger let me know what you want to do.

1

thats hard