Calculate Working Hours In Reverse

August 27, 2010 by JP · 435 words · 2 Comments

Problem: Every day I come in to work and intend to work eight hours. If I come in at 8:30 AM, and given an hour lunch, what time do I need to leave to work exactly eight hours?

Furthermore, if I come in a different time (or take a shorter lunch break), I still need to know what time to leave.

Calculate Elapsed Time

Figuring out the number of hours I worked is simple. Just subtract my start time from my lunch start time, and subtract my lunch ending time from the time I leave.

hours worked

In this example, the formula in E2 is:

**=((B2-A2)24)+((D2-C2)24)**

The problem is that this method requires trial and error to guess the correct time to leave. You have to repeatedly enter a time value into cell D2 and see if cell E2 equals eight. There has to be a better way.

Turns out there is!

We can calculate the time we need to leave using a formula. All you need to do is change E2 to a value (i.e. just enter the number of hours to be worked in cell E2) and use this formula to calculate the end time.

The formula in cell D2 is:

**=C2+((E2-((B224)-(A224)))/24)**

(it can also be written simply as =C2+((E2-((B2-A2)*24))/24))

So you enter your start time, the time you leave for lunch and the time you come back from lunch, and the formula tells you what time you can leave!

The way it works is simple: We start with the ending lunch time, and we need to add a certain amount of hours to it to reach eight total hours. How many hours do we add? The difference between 8 and the number of hours we've already worked.

In the above example, I've already worked four hours, so to get to eight I need four more hours added to the end of my lunch break (because eight minus four equals four): 1:30 PM + 4 hours = 5:30 PM.

Note that cells A2:D2 are formatted as Time values. The division by 24 in the examples above is necessary due to the way Excel treats time values.

Named Cells

The hardcoded values and vague cell references are pretty scary. So I named the cells (and added a constant called HoursPerDay which equals 24). Here's what the final formula looks like: