Formula to automatically adjust lunch hour based on start time

Hi,

I have a spreadsheet that I use to calculate attendance hours for a course we run in my organisation. As the session is a drop in this means that start times are erratic.

I created a simple spreadsheet that uses time formatted as [h]:mm. The columns are: Start, End, Lunch, Total Hrs, Total Hrs (dec).

The problem I am having is that I have a formul in the lunch column. Lunch is between 12-1pm. So I wanted a formula to automatically calculate the lunch break based on the start time. So basically any start time up to 12:00 and the lunch break will be 1:00. However if the start time is 12:01 then the lunch break will be 0.59 and say the start time is 12:50 then the lunch break will be 0:10. The reason for this is as the class doesn't resume until 1pm we don't want to count any time between 12pm and 1pm.

I used a formula in the Lunch column which appears to work ok which is:

Please Login or Register  to view this content.

Note:A2=Start Time, B2=End Time

In the IF formula I had to use decimal for time as I didn't know how to use actual time in an IF statement or if it were possible.

Dec Time
0.5 12:00
0.042 1:00
0.5416 12:59
0.5417 13:00

This is probably not the most elegant formula but what it does is: The first IF statement checks for start times before 12 noon and the 2nd IF statement for times greater than or equal to 1pm and calulates the lunch break accordingly.

The problem arises in columnd D Total Hrs. Again this is formatted [h]:mm and the formula seems to take 1 minute off the correct calulation. For example: If the Start time is: 10:45 then and the End time is:16:00, the the total time should be: 4:15, howerver the formula below shows 4:14.

Please Login or Register  to view this content.

This in turn affects the formula in column E which converts the total time in column D to a decimal value, formula:

Please Login or Register  to view this content.

I would be grateful if one of the Excel Gurus here could advise me on the best way to create a formula that will automatically calculate the lunch break based on start time and then calculate the total time minus the lunch break time.

Many thanks.

Hi All,

I have solved the issue but not in a single formula unfortunately. What I did was use the following formula and using a combination of relative and absolute cell references:

Please Login or Register  to view this content.

C2 is the lunch column. what I did was format 3 cells in row 1 and format them in the following time format [h]:mm. in cell L1 I put 12:00, in cell M1 I put 13:00 and in cell N1 I put 1:00; where these represent lunch start, end and duration respectively.

The above formula looks to see if the start time is less 12pm then make lunch 1 hour long. If the start time is greater that 12pm and less that 1pm then it subtracts 1pm from the start time, otherwise lunch duration = zero.

It's a shame I couldn't have solved this in one nice little formula instead of referencing cells but I guess at least this workaround has solved by rounding issues.

I hope this post may at least be useful to someone that needs to achieve the same result as me.

Thanks.