Felix Atagong
01-31-2008, 05:44 AM
I have a spreadsheet that calculates the total working hours of our warehouse staff, overtime, etc...
Cell C1 : starting hour
Cell D1 : ending hour
The hours are filled in, using 1904 system, without date as 08:00, 12:00, 21:00, etc... total hours is calculated as follows:
=(D1-C1+(D1<C1))
I need to divide the hours in daytime hours and nighttime hours (nightwork gets more salary). Nightshift starts at 22:00 and ends at 06:00 in the morning. I tried several formulas (MAX, MIN, combined with IF, AND OR) but they never seem to trap all possible situations, especially as midnight makes a switch from 23:59 to 00:01...
The following situations are possible (START - END)
06:00 - 14:00 - person started and ended before nightshift started on the same day
14:00 - 22:00 - person started and ended before nightshift started on the same day
15:00 - 23:00 - person started in dayshift (DS), ended during nightshift (NS) on the same day, 7 DS hours to pay, 1 NS hour.
20:00 - 04:00 - person started in dayshift (DS) day A, ended during nightshift (NS) the next day, 2 DS hours to pay, 6 NS hours.
22:00 - 06:00 - person worked 8 NS hours, zero DS hours...
03:00 - 11:00 - person worked 3 NS hours, 5 DS hours...
Any ideas for a formula? :banghead: Whatever I try there are always some situations that give the wrong results...
Cell C1 : starting hour
Cell D1 : ending hour
The hours are filled in, using 1904 system, without date as 08:00, 12:00, 21:00, etc... total hours is calculated as follows:
=(D1-C1+(D1<C1))
I need to divide the hours in daytime hours and nighttime hours (nightwork gets more salary). Nightshift starts at 22:00 and ends at 06:00 in the morning. I tried several formulas (MAX, MIN, combined with IF, AND OR) but they never seem to trap all possible situations, especially as midnight makes a switch from 23:59 to 00:01...
The following situations are possible (START - END)
06:00 - 14:00 - person started and ended before nightshift started on the same day
14:00 - 22:00 - person started and ended before nightshift started on the same day
15:00 - 23:00 - person started in dayshift (DS), ended during nightshift (NS) on the same day, 7 DS hours to pay, 1 NS hour.
20:00 - 04:00 - person started in dayshift (DS) day A, ended during nightshift (NS) the next day, 2 DS hours to pay, 6 NS hours.
22:00 - 06:00 - person worked 8 NS hours, zero DS hours...
03:00 - 11:00 - person worked 3 NS hours, 5 DS hours...
Any ideas for a formula? :banghead: Whatever I try there are always some situations that give the wrong results...