Excel spreadsheet formula problem

September 22, 2009 at 16:41:14
Specs: Windows XP Pro
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

Report •

September 23, 2009 at 05:23:12

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<B4,24*C4,0)
This formula returns 0 if the finish time is greater than the start time.
If the finish time is smaller than the start time it takes the finish time as the duration.

In both formulas the hours are calculated as 24 * the Excel duration value.
For example:
In Excel 0.5 is 12 noon, i.e., half way through the day
24 * 0.5 =12

Parts of hours are not converted to minutes in this example, but for pay calculations you probably don't want minutes, just fractions of hours.


Report •

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

Report •

Related Solutions

Ask Question