# Excel spreadsheet formula problem

September 22, 2009 at 16:41:14
Specs: Windows XP Pro
 Hi, In a time sheet I have created, there is start/end work time which works O.K. My problem is, that between 0:00 and 6:00 (night shift) there is different pay rate, so I have to calculate those night hours (using IF expression, I believe....) and represent day and night working hours separately. Question: how to make formula checking the "End Work" before midnight and split working hours in 2 different cells? Thank you

See More: Excel spreadsheet formula problem

#1
September 23, 2009 at 05:23:12
 Hi,Here are some formulas that should get you going:``` B C D E 2 Start End Today After midnight hours hours 3 22:00 6:15 2 6.25 4 6:00 14:00 8 0 ```The times in columns B and C must be pure time, no date information.You can either use data validation to ensure that the input data is always less than 1 (Excel holds time in decimal part of the number and dates in the integer or whole number part)An alternative is to add complexity to the formulas to only take the decimal part of the numbers.The formula in Cell D3 is =24*IF(C3>B3,C3-B3,1-B3)This assumes that if the finish time is 'earlier' than the start time, then the finish is after midnight.If the finish time is after midnight, The Today hours calculates the duration between start time and midnight (Excel value =1.00000), i.e. the start of the next day.The formula in Cell E3 is =IF(C4

Report •

#2
September 23, 2009 at 20:10:53
 thank you.

Report •

Related Solutions