# Thread: Time formula help (Office XP)

1. ## Time formula help (Office XP)

Here's my project. I need to track printer down time. I need to enter the date/time a printer goes down (03/10/2003 09:00) when it stars up again (03/10/2003 14:14) this will give me a total down time. BUT, I still need to get the total DOWN time in Cell A1, even if the total time is over 24 hours. In Cell B1 I need to figure out the time that is during normal work hours (9 -5) then ony calculate the prime time hours.

A1 - 03/02/2003 10:01 AM 03/03/2003 14:00 = TOTAL DOWN TIME
B1 - 03/02/2003 10:01 AM 03/02/2003 16:00 = Prime Time down time
B2 - 03/02/2003 11:00 AM 03/03/2003 18:00 = Total Time down hours down between two days.

2. ## Re: Time formula help (Office XP)

To display accumulated times over 24 hours in A1, format it as [h]:mm.

To calculate prime time hours, do you need to take weekends into account?

Perhaps you could attach a small demo workbook with some data, and an example of the output you expect.

3. ## Re: Time formula help (Office XP)

If you want the TOTAL elapsed time, do as Hans suggested, End-start and format to [h]:mm
If you want the total hours of LOST worktime (Worktime being 9-5):
Try this:
=+((INT(End)-INT(Start)-1)/3+(INT(Start)+17/24-Start)+(End-INT(End)-9/24))*24

Part 1 gives the full days, part 2 the 1st day and part 3 the last day.
I leave it to you to adjust create a function if desired.

This does NOT take into account weekends and each weekend day will be 8 hours which you will have to adjust for if desired.

Steve

4. ## Re: Time formula help (Office XP)

This should adjust and eliminate weekend days:
=+((NETWORKDAYS(Start,End)-(WEEKDAY(Start,2)<6)-(WEEKDAY(End,2)<6))/3+(WEEKDAY(Start,2)<6)*(INT(Start)+17/24-Start)+(WEEKDAY(End,2)<6)*(End-INT(End)-9/24))*24

Steve

5. ## Re: Time formula help (Office XP)

I had not though about weekends or things like that. Attached is a spread sheet that shows what we are doing now (manually) and how I would like to automate and change the spreadsheets.

6. ## Re: Time formula help (Office XP)

some suggestions:
Put the date and time in the SAME cell. You can display them both together with a format like:
mm/dd/yyyy hh:mm.

If you want to calculate the hours between the 2 times (start and end) you can simply just subtract (End-Start) which will give you the number of decimal DAYS between them. Format to [h]:mm and it will display the elapsed hours and minutes. If you want to display decimal hours then just used (End-Start)*24.

If you want to ONLY get the times between 9Am and 5 PM use my initial response. If you want to ignore weekends, use my second response.

Also setup of spreadsheet. Instead of keeping each printer in different parts of the page and having several "identically formatted" sheets, I suggest:
Make a table to hold all the info:
Col 1: Printer name
Col 2: Date/Time Down
Col 3: date/time UP again

Anything else is just calcs.

You can now use filters and subtotals to summarize info or even use a pivot table. If you want quarterly data, you could add a column to the data to give the qtr, then use this qtr col as a PAGE field in the pivot to get a summary of the downtime by printer

If you need a particular printout form, you could create it and with formulas or macros extract info from this datatable. The single datatable is much easier to use and work with than all these "multiple iterations" of the similar things.

Steve

7. ## Re: Time formula help (Office XP)

In my attachment before.. The first worksheet shows how we record it now. The second worksheet is how I am trying to upgrade it.

I like the idea of Col 1 as the printer, and col 2&3 as Date/Time Down and UP. I need to record the whole time the printer is down so Yes to a Col that would show only 9:am to 5PM down times, Yes to record all times and even weekends (we are a 24/7/365 shop). So I would also need to show it as 56:32:00 (HH:MM:SS).

From these numbers I then plan to take it a step further later. I plan on making a col to show the time that a particular printer was last up until the new down time. Try to get a idea of how long it stays up before the printer goes down again.

8. ## Re: Time formula help (Office XP)

If you are going to use hh:mm:ss,
In my formulas get rid of the "*24" at the end
Format with:
[h]:mm:ss
otherwise it will not go about 23:59:59.

Steve

9. ## Re: Time formula help (Office XP)

Ok, attached is a new spreadsheet that I am working with. Right now I can get the total down time by going to c2 and doing "=SUM(B2-A2)" to get the total down time. Later I will add a new col. and try to figure out the total down time between 8:00 am and 5:00 pm of each entry. For now I want to see if in D2 I can create a pull down to show all the possible printers to select from, but depending on the printer I select I want to change the background of the row.

Afterwards, I want to see if i can go to the E col, then depending on the Printer selected I want to go backwards and see when was the last time the printer was down and enter the UP date/time for that entry.

10. ## Re: Time formula help (Office XP)

where and how would I load this formula?

11. ## Re: Time formula help (Office XP)

For the pull down you can just use Data-validation
on a separate area of the sheet (or even another worksheet) add your list of printer names. If you want you can name this range (insert - name -define)
Then select the cells in col D (you can select the whole column if you want)
Data -validation
Allow: List
Source: put the named range or the cell addresses for the list

Now you will get an in-cell dropdown.

If you only have 3 printers, to color the rows can be done with Cond Format. Set the conditions for all the rows to formula and have something like:
=\$D2 = "Printer A" for the first etc.It will work also for 4 printers but the color of the rows for one of the printers will be the same as when it is blank. so ou could use white for NOT meeting any conditions and use the 3 conditions for the other 3 printers.

If you have more than 4, you will have to use a macro. If so you might want to just have all entries made with a macro and not even use datavalidation. You could have userform add the info directly into the next cell.

Steve

12. ## Re: Time formula help (Office XP)

Copy the fomula below and put it into a cell: If A2 has start of downtime and B2 has end of downtime and you wanted the result in G2 (from 8am - 5PM) put this in G2 (one -line):
=((NETWORKDAYS(A2,B2)-(WEEKDAY(A2,2)<6)-(WEEKDAY(B2,2)<6))/3+(WEEKDAY(A2,2)<6)*(INT(A2)+17/24-A2)+(WEEKDAY(B2,2)<6)*(B2-INT(B2)-8/24))*24
If you want decimal time (fomat as number with appropriate decimal points)
Or if you want time in hours/mins:
=(NETWORKDAYS(A2,B2)-(WEEKDAY(A2,2)<6)-(WEEKDAY(B2,2)<6))/3+(WEEKDAY(A2,2)<6)*(INT(A2)+17/24-A2)+(WEEKDAY(B2,2)<6)*(B2-INT(B2)-8/24)
Format this as [h]:mm to get hours/min.

Also you do NOT need =SUM(B2-A2), you can just use =B2-A2

Steve

13. ## Re: Time formula help (Office XP)

Steve,
Tried to copy the formual but its not working on my spreadsheet. Think you could add it the spreadsheet attached? Just haven't been able to think right the past few days.

14. ## Re: Time formula help (Office XP)

I found a couple of "bugs" in my program that this fixes:
=(NETWORKDAYS(A9,B9)-(WEEKDAY(A9,2)<6)-(WEEKDAY(B9,2)<6))*9/24+(WEEKDAY(A9,2)<6)*(INT(A9)+17/24-MAX(A9,INT(A9)+8/24))+(WEEKDAY(B9,2)<6)*(MIN
(B9,INT(B9)+17/24)-INT(B9)-8/24)

You changed the total hours (in the original) to 8 to 5 (from 9 to 5) so a std day has 9 hours not 8 and also if it goes DOWN between Midnight and 8 AM or between 5PM and Midnight it didn't subtract right. This fixes it. I added it to your spreadhseet.

15. ## Re: Time formula help (Office XP)

I see where you changed the formula and thank you. I tried to add a new line at the bottom and then I copied/paste the formula and it gives me a #NAME? error. Is there something else I need to do to make it run?

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•