+ Reply to Thread
Results 1 to 5 of 5

Calculate date based on number of hours

  1. #1
    Registered User
    Join Date
    03-05-2014
    Location
    Grand Rapids, Michigan
    MS-Off Ver
    Excel 2003
    Posts
    34

    Calculate date based on number of hours

    Hi all,

    I'm trying to calculate the due date of maintenance needed based on the number of hours the machine is ran... I had it all setup by months, but was recently told to change it (because we may work more or less depending on work load). Basically, every morning I enter in the number of hours on the machine, and it needs to tell me the due date based off that. This is what I WAS using...

    HTML Code: 
    What is the easiest way to add a row for the number of hours as the frequency due, and convert it to using that?

    Thanks!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,947

    Re: Calculate date based on number of hours

    It depends. Are you summing the hours as you enter them? Where are you entering them? Where is the limit of hours?

    Let's say that you have the last maintenance date in A2, "Hours" in B2, with the allowed number of hours in C2 - say, 1000 hours. And in D2, you enter the hours as they accumulate - it was 100, you ran for 8, then you edit D2 and enter 108. Then in E2, you could use a formula like

    =IF(B2="Hours",A2+C2/D2*(TODAY()-A2),"")

    Not sure why you are using the change event rather than formulas - you could add in the other conditions in place of the "", like

    =IF(B2="Hours",A2+C2/D2*(TODAY()-A2),IF(B2="Annually",DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)),IF(B2="Semi-Annually",DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)),IF(B2="Quarterly",DATE(YEAR(A2),MONTH(A2)+3,DAY(A2)),IF(B2="Monthly",DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)),"")))))
    Last edited by Bernie Deitrick; 02-24-2015 at 04:43 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    03-05-2014
    Location
    Grand Rapids, Michigan
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Calculate date based on number of hours

    The machine I am running has an Hours meter on it. I would enter in one generic place the current number of hours that is on the machine IE: right now there are 33,569 hours on it, lets say this number is stored in B3 just because I like it centered . Then for each item that needs maintenance, have the task listed in Column A, the number of hours the machine had on it in the last time maint. was done in column B, the number of hours allowed between each maintenance in column C, and the calculated date maint is due again in column D. Its actually quite hard to type this lol, I'm confusing myself...

    Current hours on machine
    Maint due - Hours last done - Hours between maint - Calculated due date

    Thanks!

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,947

    Re: Calculate date based on number of hours

    If the machines are run around the clock or a set number of hours per day, to calculate the next date, you would use this, assuming your first maintenance item is on row 4

    'Around the clock
    =(B4+C4-$B$3)/24 + TODAY()

    or
    '16 hours per day
    =(B4+C4-$B$3)/16 + TODAY()

    or
    '40 hours per week
    =(B4+C4-$B$3)/(40/7) + TODAY()
    Last edited by Bernie Deitrick; 02-24-2015 at 06:49 PM.

  5. #5
    Registered User
    Join Date
    03-05-2014
    Location
    Grand Rapids, Michigan
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Calculate date based on number of hours

    The machine does not run a set number of hours a day and we work random overtime on days off, so a set formula like that would not work

    Thanks though!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 11-02-2011, 06:50 PM
  2. Replies: 1
    Last Post: 11-02-2011, 06:44 PM
  3. Calculate START date based on duration (work hours) and END date
    By kaaver in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-06-2010, 12:21 PM
  4. Calculate start date based on working hours and end date
    By kaaver in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-28-2010, 06:58 AM
  5. Replies: 2
    Last Post: 01-21-2010, 11:09 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1