Excel and Dates

While recently working on some data, i recently stumbled across some formulas that i found useful:

Excel handles dates as numbers. Basically the number of days since 1/1/ 1900 0:00. The decimal portion of the number represents the hours and minutes within the day. For example today is 40556.64462. So, if i wanted to subtract the number of hours until quitting time, i'd need to subtract 40556.64606 from 40556.70833. This is fairly easy.

But, what if i have a list of events and the time they happened and i need to calculate the number of hours since each event happened and closing time (5:00 pm)? Like this:

12/22/2010 09:54
12/07/2010 10:01

If these values were in column A, i could put the following formula in B1:

=17/24-MOD(A1,1)

17/24 is equal to 0.70833 or 5:00PM. MOD is a function that gets the remainder after dividing the first number by the second number. When 1 is the second number, essentially you get the decimal portion of the first number. In this case, what i'm doing is subtracting 5:00 pm from 11:28 am. Technically, i'm subtracting these times as if they had occurred on 1/0/1900, but since all i'm interested in is the difference, it works out just fine.

So, really what i needed to do was calculate the number of business hours between one event and another (which might not happen on the same day or week).

The first thing i needed to do was split up the time periods. Essentially, there are three: (1)the number of hours on the day the first event occurred, (2)the number of hours between the first date and the second date, and (3)the number of hours on the last day. If i could calculate all these individually, i would then only need to add them together to get the total.

(1) Number of business hours on the day the first even occurred
This is fairly easy, all i have to do is find the difference between the start time the end of the day. However, if the second event happened on the same day, i wouldn't want to go all the way to the end of the day; instead, i'd just want to go to the time the second event occurred. Let's take two records for example:
12/22/2010 09:54 12/22/2010 16:42
12/07/2010 10:01 12/09/2010 11:28

If i simply subtract the first from the second, it would include after hours and some hours of the next day, which isn't part of section 1. So, i need to find which happens first: the end of the business day or the second event. Then i can take that value and subtract the first event.
In order to do this, i'll need to generate a value that represents the end of the same business day as the first event. I'll use the following formula: =A1-mod(A1,1)+19/24. This formula starts with the first event's time, goes back to midnight by removing the decimal portion, then adds 19/24ths of a day, which will advance it to 7:00pm (the end of the business day on the west coast).
So, now i have the first event's time, the second event's time, and 7:00pm on the day of the first event. All i need to do is find the smaller of the second event's time and 7:00pm then subtract the first event's time. Like this: =min( (A1-MOD(A1,1)+19/24) , B1) - A1.
For our first example record, the result is 6:48. For our second example record, the result is 8:59.

(2) Number of business hours between the start and end date
This one is a little tricky since the second event could happen on the same day, the next day, or several days later. However, all I really need to do is determine the number of business days between (not including) our two dates, and multiply by the number of business hours in a day.
Luckily, excel has a formula called 'NETWORKDAYS()' that calculates the number of business days between two dates. However, it includes the start and end dates. So, all we have to do is subtract 2. However, with our first example, this would give us a value of -2 days between the start and end. What we need to do is show 0 if they're the same or only one day different. This is easly accomplished with a max() function. The max of any number and 0 will result in the number for positive numbers, 0 for 0, and 0 for negative numbers. Problems solved: =MAX(NETWORKDAYS(A1,B1)-2,0)*12/24.
The result for the first record is 0:00. The result for the second record is 12:00 (total business hours from 8AM EST to 5PM PST).

(3) Number of business hours on the day the second event happens
This one is a little tricky. If the first and second events happen on the same day, this should be 0 otherwise, i'll be double counting. So, i'll need to calculate two things: the hours since the beginning of the day and whether or not this value should be added to the total.
Calculating hours since the beginning of the day: this one is similar to secion 1 above. Just figure out which happened later: the beginning of the day or the first event. Subtract that from the second event and I'm done: =B1 - MAX( A1 , B1-MOD(B1,1)+7/24 ).
Calculating whether or not this value should be added to the total: Really, all I have to determine is whether or not the two events happened on the same day. If they did, i can ignore this third value. I can ignore a value by multiplying it by 0. So, all i need is a function that yields 0 if the two events are on the same day and 1 if they are on different days. Get the day values of the two events and find the difference: = B1-MOD(B1,1) - ( A1-MOD(A1,1) ) or = B1-MOD(B1,1) - A1+MOD(A1,1). If the result is 1 or more, return a 1, else return a 0. You could do this with an if() function, but it's more fun with mathematics: =MIN( B1-MOD(B1,1)-A1+MOD(A1,1) , 1 ). By looking for the minimum of your logic math and 1, you'll end up with a 1 for all positive numbers, a 0 for 0, and a negative number for a negative number (which won't happen since the second event always follows the first).

Then just multiply these two values together. For the first record, we get 0:00 (since we don't want to count twice for events that happen on the same day). For the second record, we get 4:28.

Then just add all these values together. For the first, I got 6.8 business hours. For the second, i got 25.45 business hours.