Software

Office challenge: Come up with a formula that subtotals elapsed time by the day

In this week's Office challenge, learn the solution to last week's Word challenge and test your Date/Time arithmetic skills in this week's Excel challenge.

This week's challenge has two parts. Feel free to respond to either question. The worksheet below tracks elapsed time. The first problem is evident—the formula in column D (=$C2-$B2) can't handle a time period that extends into the next day (past midnight). There's a simple one-formula solution to this problem, what is it? (By the way, the format for D2:E8 is the custom format h:mm.) Now, here's part two of this week's challenge. Come up with a formula that subtotals each day in column E. In other words, in cell E2 or E4, the formula would return 2:50, for two hours and 50 minutes total on June 2, 2010. There are no restrictions, but the less work, the better.

Last week we asked…

How do you repeat an action in Word? Jbentley was the first to respond with Redo, [Ctrl]+Y. JBenton mentioned [F4], which was what I use most often. JBenton also mentioned [Alt]+Enter. A couple of you suggested writing a macro, which is a good idea if one of the shortcut keystrokes isn't adequate. A few of you mentioned Format Painter—always a good choice when just formatting is repeated. Don't forget, if you double-click Format Painter, you lock it in. Then, you can click as many spots as you like. Double-click it again to unlock it. Thanks to everyone for a great challenge!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

47 comments
Lottomaniac649
Lottomaniac649

Check my Answer Column E formula: =IF(COUNTIF(A2:$A$8,A2)>1,"",SUMIF($A$2:$D$8,A2,$D$2:$D$8)) Column D formula: =IF(B2>C2,12-B2+C2,C2-B2) I only wanted the sub-total to appear on the LAST entry for each day and I want the field to be blank if no date entry exists. I used absolute references so that this formula could be dragged down column E. it could easily be expanded to more. Lottomaniac649@smart-lotto-picks.com

James Adams III
James Adams III

I have handled this time issue by formatting the cells as hh:mm AM and using military time. This calculates my drivers elapsed time perfectly. Problem I have is when I added a row to keep a running total of overtime hours. if there is overtime it works, if not then I only get '#####'. How can I handle the negatives hours or default it to appear as a zero?

touseef88
touseef88

THE FORMULA IN CELL 'D2' SHALL BE '=IF(C2-B2

DayalanPaddy
DayalanPaddy

Hi Start End Hours 06/09/2010 10:35 08/09/2010 11:45 49.17 1. Use the formula: =(B3-A3)*24) 2. format the start and end cells as: dd/mm/yyyy hh:mm

kargall
kargall

To calculate thie over a1 day: =IF(B6

cgm48
cgm48

=IF(C2>B2;C2-B2;1+C2-B2) should do the trick for part 1

CptSpec
CptSpec

This formula works for part 1 =C2-B2+IF(B2>C2,1)

alta
alta

The easiest formula that I have used is =IF(B6>C6,C6+1-B6,C6-B6). This will calculate times over 2 days. Using the Sumif in Column E as stated in comments above is also what I would use to calculate the total of hours for the day.

RU7
RU7

Here is a solution to the second part of the problem that does not depend on the entries being in chronological order. Format should be [h]:mm to show times longer than 24 hours. =IF(MATCH(A2,$A:$A,0)=(ROW()),SUMIF($A:$A,A26,$D:$D),"") The match finds the row number of the first instance of the date in the current row. If the current row is the first instance, we do a sum of elapsed time for all instances of that date. If it is not the first instance of the date, the cell is blank.

hycm53
hycm53

first name entire range B for Start, Second name entire range C for End. Validation for entire range B & C for "Valid Time Forma using HH:MM Format". the formula for range C (Elapsed Time)is "=If(End >Start, End-Start, (1+End)-Start)". Date Start End Elapsed Subtotal Time by Date 6/2/2010 9:10 AM 11:15 AM 2:05 6/2/2010 1:00 PM 1:15 PM 0:15 6/2/2010 3:00 PM 3:30 PM 0:30 2:50 6/3/2010 8:00 AM 10:20 AM 2:20 2:20 6/4/2010 10:30 PM 1:10 AM 2:40 2:40 6/5/2010 2:00 PM 2:30 PM 0:30 6/5/2010 4:30 PM 5:30 PM 1:00 1:30

athineos59
athineos59

Column C formula: =IF(C5 < B5, (12-$B5)+12-(12-$C5), $C5-$B5) Column D formula: =D4+D3+D2 I think that SumIf works if the range stays the same. What if the range changes? You would have to keep changing the formula. The above is simple and quick enough to repeat as necessary.

kirk.bare
kirk.bare

Deleted Sorry. the formulas did not copy correctly.

richard
richard

In cell D2, enter the formula below and copy down to D8 =(C2+1*(C2

kreniska
kreniska

Here is a single formula that works: =IF(C2 < B2, 1+C2-B2, C2-B2) Also, this will work =1+C2-B2 This works because the format of the Elapsed time column is not showing the days, just the hours and minute. If you create another column with the formula =VALUE(D2) you will see why the first formula is the best.

jbenton
jbenton

assuming that a time in column C less than that in column B always means the next day and that a greater time always means the same day then =mod(c2-b2,1) in cell D2 (and copied down) is probably the briefest solution to the elapsed time problem

dennis
dennis

Well, the first part is pretty simple, although the basic format of the worksheet could use a little work ... it makes a lot of assumptions, therefore the solution must also make assumptions. Assuming the only "problem" finish time is simply past midnight, the simplest way I know of is to ask 'if' the finish is less than the beginning, and if so, add 1 to the finish time. Excel thinks in days, so its that easy to account for the added day. =IF(C11 'less than' B11,(C11+1)-B11,C11-B11)" As to the second part of the challenge, well there are some less elegant ways (usually what I end up doing), but I need to think more about it. ... Kind regards

bryan.miller
bryan.miller

Formula for the 1st part - to get the time calculation right =IF($C2

Kent Lion
Kent Lion

The problem is poorly stated, but that's normal when someone comes to you and asks "how do you...". To correctly respond, I would have to know the constraints on what can be in columns A, B & C (e.g., will adjacent values in Column A always be 0 or 1 day apart, and if a span ends on the next day, to which day's time should it be added?). With the constraints implicit in the given data only: Column D: =$C2+IF($C2

mla_ca520
mla_ca520

Column D formula: =IF(ISBLANK(C2),"",IF( ISBLANK(B2),"",IF(C2<B2,((C2-0)+(0.999305556-B2+0.00069444)),C2-B2))) The first part leaves cell blank if there is no value in cell C2 the next part leaves the cell blank if no value exists in cell B2. It then checks to see if C2 is greater than B2. If so it subtracts the 12 am from C2 and adds that to the value of (11:59 pm - B2 + 1 minute) [subtracting B2 from 0 didn't work well]. This formula can be dragged down column D and it will work for each cell. Column E formula: =IF(ISBLANK(A2),"",IF(A2A1,SUMIF($A$2:$A$20,A2,$D$2:$D$20),"")) I only wanted the sub-total to appear on the first entry for each day and I want the field to be blank if no date entry exists. So it starts by checking for a blank cell in the A column and returns a blank cell if A is blank. Next it checks to see if the current row in column a is different than the preceeding row in column a. If it is the same the formula returns a blank cell otherwise it uses a SUMIF to return the totals in column D for everything with a date matching the value in the current row of column A. I used absolute references so that this formula could be dragged down column E. I limited this to 20 rows but it could easily be expanded to more.

jgarcia102066
jgarcia102066

Assumption: When the end time value is less than the start time value, the end time value implies the next day. Here's my column D solution (specifically in cell D2 then copied down): =IF(C2 > B2, C2-B2, (C2 + 12 - B2)) The formula first checks if the end time value is greater than the start time value (same day) and if it is, you simply subtract the start time from the end time. If the end time value is less than the start time, then add 12 (hours) to the end time and subtract the start time. If you try a start time of 2:00 AM and an end time of 1:00 AM you will get 23:00 hours. Thanks to oldbaritone's comment, I realized that I need to add some more information: Given that I only see time in columns B and C I formatted the columns as Time which is why adding 12 before subtracting works. For the second part I used the following formula in cell E2, then copied down. =SUMIF(A:A,A2,D:D) This is much more difficult for me to explain. It's better to lookup the SUMIF function to decipher what is happening.

TobiF
TobiF

You may consider taking your email address out of your post. If you still want to somehow be reachable, you can enable private messages in your profile.

Glenn from Iowa
Glenn from Iowa

It took me a while to figure out what you were doing, but I assume your overtime column subtracts 8 hours (8/24 for Excel date/time cells) from the Subtotal by Date column. Therefore, if the person works 8:30 that day, the Overtime column will show 0:30, but if she works 7:30 the next day, it would be -30 minutes. So here's the "it depends": If you're not using the Overtime column for calculations, but just as a visual flag, change the format of the cells to [h]:mm;"0:00" However, if you want to subtotal the overtime hours also, try a formula like this one (assuming Subtotal by Date is column E):=MAX(E2-(8/24),0) Edit for formatting

RU7
RU7

you don't have rows sorted by date? This could happen if there was a column with persons' names, company names, service areas, or the like. Sorting on that column could give non-chronological dates.

RU7
RU7

Put the formula in cell E2, then select the cell and double click the fill handle (the little black square at the lower right corner of the cell). That should copy the formula down to the last row of your data. Then you select the whole range and turn it into a table so the table and its formulas replicate to the next row as soon as new data is entered in it.

RU7
RU7

Here is my graphical attempt (Start, end, and difference displayed of the hours of the day). Normal S------------E 123456789012345678901234 (E-S correctly gives the area indicated by "-") Spanned minight -----E+++++++++S-------- 123456789012345678901234 (E-S gives the negative of the area indicated by "+". Adding that to the whole day correctly gives the area indicated by "-") So we have 1+E-S, and we really don't need parentheses. As has been shown before it could be IF(E>S,0,1)+E-S.

evsanten
evsanten

if you change the beginning cell in each range to fixed the formula has general applicability without knowing the number of rows beforehand. =IF(A2A1,SUMIF($A$2:A2,A2,$D$2:D2),"")

ian
ian

I am only here to learn from you guys but from a lay persons point of view, I see a lot of assumption. One assumption is that a day is 24 hours, technically yes but the problem asked for a "a time period that extends into the next day" . This could legitimately be as much as 47hrs 58mins starting one minute past midnight on day one (6/23/2010 00:01) to one minute to midnight on day two (6/24/2010 23:59). More if we want to be pedantic and go down into nanoseconds, but you see my point?

mmoser
mmoser

I need to calculate time for a two column range where we put in date and time, and need to calculate the number of hours in between the two dates and times, but need to exclude weekends and holidays. Also need to understand how the formula works.

mla_ca520
mla_ca520

I think that it's unlikely for a time to span more than two dates on a time-sheet. If someone submitted a timesheet to me with more than 12 hours of straight time. I'd tell them not to do it again because their focus has to be so bad after 10 or 12 hours that I'm not willing to pay for it.

agency
agency

I made the assumption that columns B and C did not contain the date as well as the time so my column D formula is: IF(C2 < B2 , C2 + 1 - B2 , C2 - B2) My column D formula is then: =SUMPRODUCT(($A$2:$A$8=A2)*(ROW($A$2:$A$8)

Joaquim Amado Lopes
Joaquim Amado Lopes

... in the subtotal formula, so that the subtotal only appears on the last line for each day: =IF(A2A3,SUMIF(A:A,A2,D:D),"")

oldbaritone
oldbaritone

Doesn't date/time get stored as days - the integer part is the number of days, and the fractional part is hours/minutes/seconds? So to fix the 1:15 AM issue, don't you add 0.5 (days) instead of 12 (hours)? The simplest way, from a programming standpoint, is to use full date/time instead of just time. Then the subtraction would always work correctly, and a negative result would indicate a user data entry error.

RU7
RU7

Assuming you chronological date-time pairs in successive rows, and want to calculate the elapsed time from row to row, use this function for the dates, then handle the hours. With one row of headings, the dates are in column A, the times are in column B, and elapsed time is in column C. In cell C3: =NETWORKDAYS(A2,A3)*24+(B3-B2) Format column C as [h]:mm to handle instances > 24 hours.

RU7
RU7

this makes a running subtotal for a row's date, not just a single subtotal in the first or last row for a given date.

Glenn from Iowa
Glenn from Iowa

The formula above that adds 12 to the End time works fine with the given example. But if you use the format [h]:mm for the time values (which gives the total time elapsed in hours, as opposed to h:mm, which modifies any times over 24 hours), you will get a value like 266:40. Also, how likely is it that you would want a total of the hours for the week? To find the elapsed time, use this formula (in D2, then copy down): =IF(C2<B2,1,0)+C2-B2 Joaquim's formula will work perfectly well, but this equivalent formula has one less character (is that less work :-) ): =IF(A2=A3,"",SUMIF(A:A,A2,D:D)) To sum the times, just add the formula =SUM(D2:D8) to cell D9.

RU7
RU7

If the day was included in the format of the data columns, B and C, with a format like this: mm/dd/yyyy h:mm AM/PM, the original formula, =C2-B2, would work. This also addresses the multiple day span problem. Which, btw, could easily happen for this challenge because the problem does not specify that this is hours worked. However, as the problem is stated, there is no way to indicate multiple day spans. For that matter, the value in C6 is not valid, the only date associated with it is 6/4/2010, but, assuming something cannot end before it starts, that cannot be correct. From the given information, only one assumption is valid. Since the dates appear to be in chronological order, and date in the next row is one day later, the end time for C6 is most likely on 6/5/2010.

jgarcia102066
jgarcia102066

The data provided in the spreadsheet (because there is an error D6) indicates that columns B and C are not recorded as full date/time values but rather as only time values or as the same day given supplied in column A. The original formula that the author supplied would have worked had the data been recorded as you suggested. Also, since I did not see any dates, I formatted columns B and C as time which is why adding 12 hours works. If you configure B and C as date/time then the 0.5 you suggested would work. Looks like I should have added more items to my assumptions. :)

agency
agency

If that's what you want amend the function: =IF(OR(OFFSET(A2,1,0)>A2,ISBLANK(OFFSET(A2,1,0))),SUMPRODUCT(($A$2:$A$8=A2)*(ROW($A$2:$A$8)

pkrishna43
pkrishna43

"how likely is it that you would want a total of the hours for the week?" Isn't it obvious? Total number of hours worked in the week. To get paid.

Joaquim Amado Lopes
Joaquim Amado Lopes

By adding 12, you're adding 12 days, not 12 hours. But, as the result is in hours and minutes, the excess 11 days don't count. It produces the same result as long as you use an integer above zero.

Editor's Picks