Help: calculating hours for various equipment

**RiverTam** · 01-03-2007, 05:06 PM

Hiya. I just found this board, and I hope you can help me! I've got a spreadsheet that lists multiple pieces of equipment and when it was worked on or down. It has the following columns:

Date
Jeep #
Start Time
Stop Time
Total Repair Time
Other Downtime (which is most often blank, as below)

7/18/06.......35.....1:00 PM.....2:00 PM.......1:00
7/20/06.......33.....1:00 PM.....2:00 PM.......1:00
7/28/06.......35.....9:00 AM....11:00 AM......2:00
10/18/06.....50.....6:30 AM.....7:00 AM.......0:30
10/18/06.....33.....9:00 AM.....11:00 AM.....2:00

(I hope the ... is okay, couldn't figure out how to make the columns line up.)

Okay. So now, I can use Sheet Two for the next part, but I don't know how to to get a total time per month for each seperate jeep. Most of the time, there will only be one record per month per jeep, like the second line above. But lines one and three are for the same jeep, and so I need the formula to add column E up only for those two lines.

I hope this is making sense, and I hope someone can give me a hand please. If you need any more information, please let me know. Thanks!

**Maistrye** · 01-03-2007, 05:15 PM

You'll need 3 columns on your second sheet, one for the year, one for the month, and one for the jeep number. I'll assume Jeep # in column A, month (as a number) in column B, and year in column C... with header rows in row 1.

To make things simpler, it would be useful if you used named ranges to refer to the columns on Sheet1 for the RepairDate, JeepNum, and NetTime. (If you've never used named ranges before, see http://www.contextures.com/tiptech.html )

RepairDate, JeepNum, and NetTime all have to have the same number of rows. Essentially:
RepairDate = 'Sheet1'!$A$1:$A$100
JeepNum = 'Sheet1'!$B$1:$B$100
NetTime = 'Sheet1'!$E$1:$E$100
Where 100 is whatever number of rows you want to look at.

In D2 (on Sheet2), put the following formula using the above named ranges:

Please Login or Register  to view this content.

You can then fill this formula down.

HTH,
Scott

**RiverTam** · 01-03-2007, 06:00 PM

Thank you so much! Unfortunately, I may need slightly more detailed instructions... Excel's definately not my favorite program.

Originally Posted by Maistrye

You'll need 3 columns on your second sheet, one for the year, one for the month, and one for the jeep number. I'll assume Jeep # in column A, month (as a number) in column B, and year in column C... with header rows in row 1.

Okay, this I did.

Originally Posted by Maistrye

To make things simpler, it would be useful if you used named ranges to refer to the columns on Sheet1 for the RepairDate, JeepNum, and NetTime. (If you've never used named ranges before, see http://www.contextures.com/tiptech.html )

After checking out that link, I did this as well.

Originally Posted by Maistrye

RepairDate, JeepNum, and NetTime all have to have the same number of rows. Essentially:
RepairDate = 'Sheet1'!$A$1:$A$100
JeepNum = 'Sheet1'!$B$1:$B$100
NetTime = 'Sheet1'!$E$1:$E$100
Where 100 is whatever number of rows you want to look at.

Okay. I understand what you're saying here, but do I have to specify this somewhere in either Sheet1 or Sheet2?

Originally Posted by Maistrye

In D2 (on Sheet2), put the following formula using the above named ranges:

Please Login or Register  to view this content.

You can then fill this formula down.

And then, you lost me. ^^; I did copy that code and set it in my Sheet2 in D2, but it was a blank sheet (except for the headings that I added)... so what did I not do to make this wonderful formula work for me?

Thank you soooo much for helping me out with this, and for your quick response. Zai jian!

**Maistrye** · 01-03-2007, 07:36 PM

I'm sorry... I mislead you. The way I was thinking will work... but I don't think it's the best way for you to do it, if I understand what you want.

You basically want the data summarized for you, right?

What you can use is a PivotTable. They're fairly friendly once you get the hang of them. And they are simpler in the long run than what I was going to suggest. They are also simpler to manipulate if you want different layouts, etc.

That site I gave you has several pages of information on using PivotTables.

I'm trying to think of a quick way to explain how to do this. :-)

It's probably best if you start from scratch (ie. without the changes I suggested earlier). (Sorry)

On Sheet1, add two columns (F & G are good enough, or insert them somewhere else). Call them Month and Year. In the month column, put the formula =Month(A2) and fill down. In the year column, put the formula =Year(A2).

It's probably best if you set up a dynamic named range to refer to your source data. (That way, it allows for it to be expanded as time progresses) This will have to be explained to you... I have a link for it somewhere, but I'll have to find it. For now, it can be skipped.

Then, you go Data -> PivotTable and PivotChart....
Microsoft Office Excel list or database should be selected on the top, PivotTable on the bottom.
Then, select Next.
This is where you enter the source. For the time being, select the region that has the data you are using on Sheet1. (This can later be replaced with a dynamic named range which will adjust as you enter new data.)
Once you've selected the range, select Next.

At this point, you'll need to select Layout.
You'll have to drag the Year, Month, and Jeep labels into the square "Row". (You may find other arrangements more suitable to you)
Drag the RepairTime Column into the Data field. If it doesn't say "Sum of Repair Time", double-click it and choose the Sum function.
Then hit OK.

And finally hit Finish.

You should have approximately what you're looking for. You'll probably have to play with it and read through that site I suggested to get a better understanding of what they can do.

Keep in mind that the source for this PivotTable should probably be a dynamic named range (which me or someone else will get you info on). The other point to remember is that when your data is updated, you need to refresh your PivotTable.

This is probably a lot of information... you'll have to indicate where you need help. :-)

Scott

**RiverTam** · 01-04-2007, 06:23 PM

Thanks again so much for your help. Yes, a Pivot Table seems like it will work perfectly, and I've been messing around with it all day, reading those links and formatting and such. Except now...

Okay, for some reason the new Pivot Table isn't computing correctly. After I put the Jeeps on the left column, and the month and year along the top row, and then tell it to use the Repair time as the data, it's not putting any of that in the "time" format it's supposed to be staying in. Which means it's not calculating right, because it's either ignoring or rounding up the half hour increments; plus, one value is just changing from two hours in the original table, to one hour in the Pivot Table, and I have no idea why.

I'd like to attach what I have in case you'd like to see it (though I don't know how yet... oh, there, found it in the forum, I have to .zip it); otherwise, if you have any more direction (or perhaps more links, I didn't read /everything/ about PT's so maybe I'll do so tomorrow) it would be much appreciated.

Another thing I wanted it to do, but couldn't make it, is writing out the months in words in the PT instead of just having numbers along the top -- /I/ know what they mean but my boss might be confused when I show him.

(As I saw in another thread... I learned something new today, can I go home now? *sniff*)

~RiverTam

**Maistrye** · 01-04-2007, 06:41 PM

The problem with the values not displaying properly is two-fold: If you look in the top left corner of your Pivot Table, you'll see "Count of Total Time". This means it's using the Count function to summarize your data... which won't do what you want it to do. :-)

Put your mouse over top of "Count of Total Time" and right click, then choose Field Settings. In the field settings, choose Summarize By: Sum. (That will make it so it adds them all up). Next choose Number... and choose the Time format 13:30 (or whatever you want). Then hit OK enough times to get out, and it should work fine.

The month thing, instead of using the formula:
=MONTH(A2)
Use the formula:

Please Login or Register  to view this content.

Don't forget to refresh after making that change. (If you want the month to display differently, change the text above.) Someone may have a better way to get the text for the month, but that's all I could think of.

NOTE: I'm not certain how well the Months will sort in the PivotTable... you'll probably have to play with that. (Excel might have a default sort if it sees month stuff, I don't know)

Scott

**RiverTam** · 01-05-2007, 03:21 PM

Thank you thank you thank you! Wow, that looks perfect. And now I'll be able to hand them a nice little report whenever they ask. Plus, I learned about Pivot Tables! You've saved the day.

I know exactly where to come next time I have a problem with Excel. THANK YOU again.

Zai jian! ~RiverTam

Edit: Hmm.... one thing I haven't been able to make it do is list all the jeep numbers even when there's no data there... as of now I only need to list 2007 values, and since not every one has even been worked on yet this year, it makes my table tiny. Any ideas? Thanks!

**Maistrye** · 01-05-2007, 03:46 PM

There is one more thing you might want to do, use a Named Range to describe your data. You don't have to set it up to be Dynamic (so that it automatically adjusts), but you should use a name.

The same site has one link on "Dynamic Ranges, Naming". Essentially, you can put the following formula in where she has her example: (Probably name it JeepData or something)

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),9)

Note that this means you can't have blank lines in Column A, otherwise it will miss lines at the end. If you want blank lines, this formula will have to be modified.

Once you've done this, go to your PivotTable, right click on it and choose PivotTable Wizard. Hit Back. Type in JeepData in the cell instead of the cells it says. Then hit Finish.

This way, whenever you Update, it will grab all the data.

Scott

**Maistrye** · 01-05-2007, 03:57 PM

I don't know a 'perfect' way to do this, but here's one that will work. (Note you might have to change the date if you're going to limit the data in the PivotTable to a particular month.)

Put all the jeeps at the beginning of your table (that way, if you want to change the date, you can easily).

Put the date to be one that already exists, such as 1/1/07. Don't put anything in the TotalTime field. Put the formulas for month and year in their respective columns.

As long as January 2007 is in the data you're selecting, this will work fine. If you find you're limiting it to a different month, change the date for these 'extra' rows.

Scott

**RiverTam** · 01-05-2007, 05:56 PM

Wow... I'm so excited that I learned how to use these, they're really very neat. A million more thanks for all of your help Scott, I would have been lost without you.

So, after showing my boss what we had come up with for the repair time table, he said "Oh, that's nice, but really we need to track the downtime." *blink* What? I've just spent two days trying to figure this out and it's the wrong thing? "Oh, no, we can use this too, for a different boss." Thank goodness.

So I seperated the two data sheets at that point, one for repair time, one for downtime, and made two different Pivot Tables. After almost crying (and almost reposting) about why my dynamic naming wasn't working, I finally looked closer at the code I was using and realized that my sheets weren't being called Sheet1 anymore, and there was the problem -- now, it works PERFECTLY and I can update the tables and it's wonderful.

But of course...... I ran into another snag. Downtime. I have it set up just like the repair time, except the repair guy is only here between... oh, 6:00 AM and 5:00 PM. The =(D2-C2) formula works fine for this. But machines (like one tonight, in fact) can be down from, say, 1:30 PM - 11:00 PM today (when second shift leaves and we close up for the weekend), and then it counts as down when third shift returns Sunday night, at 11:00 PM, until Monday morning, when it gets fixed, at 8:00 AM.

Well, let me tell you, Excel does /not/ like me when I try to get it to subtract 8:00 AM - 11:00 PM. At the moment, I have it calculating in sections: 11:00 PM - 11:59 PM (Sunday), then 12:00 AM - 8:00 AM (Monday). But that's giving me a dumb total of 9:59.

Okay Sensei, I await your guidance. And hey, I'm a freelance editor in my spare time, so if you ever need anything looked at free of charge, I'm there. (I'm waaay better in Word.) You're making me look like a genius to my boss. Zai jian!

(Edit: P.S. I also followed your instructions about getting the jeeps to all list even if they're blank and that's perfect too, yay!)

**Maistrye** · 01-06-2007, 02:03 PM

You realize you don't really need to split the tables into two different worksheets. :-) You can generate two different PivotTables off the same data, if you are so inclined. (ie. you just use a different set of columns) This has the benefit of making it so you don't have to re-enter the same data over and over.

As to your problem... one thing you can do (which will make it slightly harder to enter the data) is use a different date format, something like 3/14/01 1:30 PM or 3/14/01 13:30 or a custom one, like [$-409]dd-mmm-yy h:mm AM/PM;@ (just underlined so you know where it begins and ends) or [$-409]dd-mmm-yy h:mm;@ (These later two express date/time in the 14-Mar-01 format instead of 3/14/01, otherwise they are the same as the default Excel ones). If you have the full date, it makes it easier to subtract and get the full time between two dates. But, this also means you have to enter the full date each time, which may be a nuisance. I'd also recommend using the format [h]:mm;@ to display the result. (The Excel default one 13:30 only goes up to 24 hours, and downtime may extend longer than 1 day) Excel has a default one that shows the hours over 24, but it also shows seconds, which are inconsequential (37:30:55), so I'd recommend the custom one over this one.

As to subtracting your non-working times (ie. from 11pm Fri/Sat to 11pm Sun), there are different ways to do this. Some are quite complicated, some are simpler. For now, I'll only mention the simpler.

(I'll assume your times are in C2 and D2 for the following, and I'll just randomly pick column K for the extra one, obviously you can switch these)

If these periods are always the same length and some multiple of a day (ie. not partial, either 1 day or 2 days or 3 days), an easy way is to have an extra column where you manually pencil in the # of non-working days every time a machine's downtime overlaps a non-working period. (For example, K2 = 2) Then, for your formula, just subtract this column. ie. =D2-C2-K2. This works because the date format is derived from a decimal number, and 1.000 is equivalent to 1 day.

If they're not always an even number of days (ie. some are 16 hours, some are 40 hours), you can use this column to pencil in the number of hours that the machine was down during non-working times, for example, K2 = 48. Then your formula becomes: =D2-C2-(K2/24) (An extension of above, 1/24 = 0.04166667 is equivalent of 1 hour, and so on, which)

Note that for both of the above, the column K is not using a Date/Time format. It's just a straight integer representing either the days or hours of downtime, depending on which you choose.

Not knowing how your business works, I'd recommend doing it manually, particularly if you don't have a large number of items that are down over the weekend. The reason for this is if the off-work periods vary in duration, the calculation can become quite complicated.

Scott

**RiverTam** · 01-08-2007, 04:48 PM

Happy Monday Scott, I was actually looking forward to work today because I like puzzling out problems like this, and wanted to see your response. (Of course, that little bump is over and now I wish it were Friday again...)

Okay. So first off, I did know I could use the same sheet to make two Pivot Tables but they really are two seperate sets of data for two different uses, and there won't be any overlap or entering of data twice. Plus, I won't always be entering the data, and my not-so-computer-literate boss appreciates the seperate sheets (specifically labeled). But thanks!

And now to the time problem. (Okay all these changes to follow are being done in a copy of the table 'till we get it tweaked.) So, I want to use your suggestions of changing the date format in the downtime table to 3/14/01 1:30 PM for start and stop, that will be easiest because machines are down over multiple days enough to warrent it. This eliminates the need for the first "Date" column, so I've changed the "Month" and "Year" columns to take from the "Start" column, but what will happen if say, a machine is down Jan. 31-Feb. 1? I assume we'll just have to pick one, and if it goes down in Jan., count it as Jan. But let me know.

Unfortunately, when I ask the Downtime column to =(D2-C2) when the times are (C2) 1/5/07 1:30 PM and (D2) 1/8/07 12:00 PM, it's giving me the answer of 22:30. That's, um, wrong. (And I am using the [h]:mm;@ number/time format also.)

Next, the non-working hours are not always in days, so we'll use hours, and that was a great idea by the way. I've also changed the formula now to =(D2-C2-(H2/24)) but that doesn't seem to be calculating right or at all either, probably because of the above problem.

I hope you can help! Thanks! ~RiverTam

**RiverTam** · 01-08-2007, 05:52 PM

Hiya. So, besides the problems with the time above, now I'm having a brain freeze and can't remember how to make each new entry (row) use the same formulas as the one above it (so I don't have to type "=(D15-C15)" each time, it'll just calculate that when I enter two times in C and D). I know I've done it before, and I just recently read it again when I was looking at that Contextual site you linked above, but I can't for the life of me find it again and it's driving me a bit bats. Thanks for the reminder. ~RT

**Maistrye** · 01-08-2007, 07:37 PM

Originally Posted by RiverTam

And now to the time problem. (Okay all these changes to follow are being done in a copy of the table 'till we get it tweaked.) So, I want to use your suggestions of changing the date format in the downtime table to 3/14/01 1:30 PM for start and stop, that will be easiest because machines are down over multiple days enough to warrent it. This eliminates the need for the first "Date" column, so I've changed the "Month" and "Year" columns to take from the "Start" column, but what will happen if say, a machine is down Jan. 31-Feb. 1? I assume we'll just have to pick one, and if it goes down in Jan., count it as Jan. But let me know.

Yup, you'll just have to pick one. This will distort slightly your monthly totals. In general, it should average out in the long run. Another alternative is to stop every line at the last day of the month, and restart it for the beginning of the next month. This will artificially inflate the number of repairs you do though. Your method is easier.

Originally Posted by RiverTam

Unfortunately, when I ask the Downtime column to =(D2-C2) when the times are (C2) 1/5/07 1:30 PM and (D2) 1/8/07 12:00 PM, it's giving me the answer of 22:30. That's, um, wrong. (And I am using the [h]:mm;@ number/time format also.)

The bad news is... it worked fine for me when I entered it. That is, it worked with [h]:mm, but gave your answer with h:mm. I'd suggest trying again.

Originally Posted by RiverTam

Next, the non-working hours are not always in days, so we'll use hours, and that was a great idea by the way. I've also changed the formula now to =(D2-C2-(H2/24)) but that doesn't seem to be calculating right or at all either, probably because of the above problem.

Yup, you got it. This will work once you figure out what's wrong with the formatting of the Downtime.

Originally Posted by RiverTam

Hiya. So, besides the problems with the time above, now I'm having a brain freeze and can't remember how to make each new entry (row) use the same formulas as the one above it (so I don't have to type "=(D15-C15)" each time, it'll just calculate that when I enter two times in C and D). I know I've done it before, and I just recently read it again when I was looking at that Contextual site you linked above, but I can't for the life of me find it again and it's driving me a bit bats. Thanks for the reminder. ~RT

There are a couple ways to do this:

1) Move your mouse over the bottom right corner of the cell, until it shows a black plus sign. Click and drag down.

2) Highlight the section you want the formula to go in, with the cell having the formula at the top. Hit Ctrl+D. (Same as Edit->Fill->Down)

And I left you with the puzzle of figuring out why Downtime is not displaying properly. :-)

Scott

**RiverTam** · 01-09-2007, 04:25 PM

I'm an English major, math and formulas are not my friends. As it turns out, yeah, 22:30 /was/ the correct amount of time -- except the reason I was confused is because it's automatically taking out 48 hours (for the time between 11pm Friday to 11pm Sunday, or honestly I assume 12am Sat. to 12am Mon.) even when I don't put anything in the H (Non-working hours) column!

I tested this with a different set of time; Friday 3pm start, non-working hours start at 11pm Friday (so that's 8 hours downtime); then it comes back up at 11pm Sunday (for 48 hours non-working hours) and has a stop time of 7am (another 8 hours down). The answer is 16 hours, but before I even tell it that there were 48 non-working hours, it's giving me the answer of 16!!!

This might not be a problem, but if people come in on Saturday (when we have enough work) then there won't necessarily be 48 non-working hours every weekend, there would be less!

I'm going to attach the updated file; if you're getting sick of me, I understand, it's been about a week now!

But if you can help a bit more; once we get this settled I can make the Pivot Table myself and leave you alone.

Thanks. ~RT

P.S. I did this:

1) Move your mouse over the bottom right corner of the cell, until it shows a black plus sign. Click and drag down.

But I would rather not have to choose a fixed number of rows for this to apply to, in case we use this spreadsheet forever and I'm not around to fix it when they reach the 200th row or whatnot. Ideas?

**Maistrye** · 01-10-2007, 12:47 PM

I looked at your formats... and as it says in the file attached, you were using the default Time format (13:30). That said, if you look in the Custom portion, you have the Time format ([h]:mm;@) set up. (ie. you entered it properly, you just weren't using it.)

Anyway, to fix it, take your original (that way it doesn't have my extra lines). Select all of column D. Format -> Cells... Then choose Custom and the [h]:mm;@ format. Then make sure to hit Ok. If it doesn't show 70:30 on line 13, you'll have to try again.

If it still doesn't work, take the one I sent, delete the extra lines I added. It's better for you if you can fix it on yours though.

As to the problem of the formulas... copy them down 2,000 or 3,000 lines if you want.

Honestly though, if they're using your spreadsheet and run out of lines and you're not working for them anymore, they'll eventually ask someone who knows something about Excel and will be able to show them how to copy the formulas down. You could do it with macros, but that will probably cause more problems than it will solve. (ie. you and most people can eventually figure out what's wrong with formulas, but if there is a macro problem, you may be stuck simply because it's not been part of your training.)

If you leave, it doesn't hurt for others to be motivated to learn a little bit about Excel, and copying formulas down is really a simple task once you've done it a few times.

If you really doubt the abilities of your fellow employees, or future fellow employees, you can include a worksheet tab called Instructions, wherein you can put the basics of how to do the basic operations in the spreadsheet, like refreshing the PivotTable, entering data into the spreadsheet, copying down formulas, etc.

Ahh, the tough love approach. :-)

Scott

PS

In the file, the comment isn't appropriate anymore... I wasn't going to format the whole column, but changed my mind and forgot to change the comment. Compare the format in column D in my file to the format in column D in yours.

**RiverTam** · 01-10-2007, 03:55 PM

Well well well. Now it works fine, after selecting the column and going to Format -> Cells, but I have no idea why it wasn't working, or rather, staying, when I right-clicked after selecting the whole column and clicked Format Cells. Oh well, no big deal.

The Instructions tab is another great idea, I will be doing that, and while I'd love to leave my boss's bosses in the dark when I leave, I don't want to put my boss in the position to explain. (See, English major... and also, /when/, not /if/, I leave...)

Last... I hope?... question, fine I'll copy the formula down 2000 lines, but how do I make it not show 0:00 in all the not-yet-used lines? Thanks. ~RT

P.S. I should mention that I have selected "Fill Formatting Only" from the ... whatever it's called box, but I find that clears the formula, so... what am I doing wrong?

**Maistrye** · 01-10-2007, 04:15 PM

To do that, you take your formulas and encapsulate them in an IF statement.

Basically, you pick the cell you're going to use to test to see if you have to show the data on the row. I'll just guess and say that if the cell in column A has a value, then you want to show the values for the formula. (You can adjust this) The FORMULA bit you replace with your actual formula.

In Row 2:
=IF(TRIM(A2)="","",FORMULA)

So, if your formula is =D2-C2-(K2/24), you'd put:
=IF(TRIM(A2)="","",D2-C2-(K2/24))

The only problem you'll find with the instruction tab (which I've found in the past....) is people tend not to read them. I like to make my stuff foolproof, but fools work hard to make it impossible. (joking... most of the time, lol)

Scott

Help: calculating hours for various equipment

LinkBack

Thread Tools

Display