+ Reply to Thread
Results 1 to 11 of 11

Calculating Number of Days for job length periods

  1. 03-16-2009, 07:12 PM #1
    Joe B
    Joe B is offline
    Registered User
    Join Date
    03-16-2009
    Location
    Lusby, Maryland
    MS-Off Ver
    Excel 2007
    Posts
    10

    Calculating Number of Days for job length periods

    I'm trying to calculate the length of a work order to develop an average and future proposal estimates.

    For example:
    Job received = A1
    In work = B1
    Job completed = C1
    Total days to complete = D1

    I know I can enter in D1 C1-A1 and get the correct result but I want the field to calculate A1-TODAY() until a completion date is entered. This would display the number of days the job has been in work until completed. Once completed use the C1 for calculation.

    Sorta like
    If(C1=">=0" then (A1-Today()) else C1-A1)

    I will then use conditional formatting with block colors to indicate whether it is a completed job or in work job

    TIA

    Joe
    Last edited by Joe B; 03-17-2009 at 07:12 AM. Reason: marking Solved!!!
    Reply With QuoteRegister To Reply
  2. 03-16-2009, 07:39 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Calculating Number of Days for job length periods

    Try this in D1:

    =IF(C1<>"",C1-A1,TODAY()-A1)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 03-16-2009, 10:22 PM #3
    Joe B
    Joe B is offline
    Registered User
    Join Date
    03-16-2009
    Location
    Lusby, Maryland
    MS-Off Ver
    Excel 2007
    Posts
    10

    Thumbs up Re: Calculating Number of Days for job length periods

    Quote Originally Posted by JBeaucaire View Post
    Try this in D1:

    =IF(C1<>"",C1-A1,TODAY()-A1)
    You are awesome, thanks for the quick response!!! It worked perfectly. Thanks

    Joe
    Reply With QuoteRegister To Reply
  4. 03-16-2009, 11:29 PM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Calculating Number of Days for job length periods

    My pleasure. If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]
    Reply With QuoteRegister To Reply
  5. 03-17-2009, 04:27 AM #5
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculating Number of Days for job length periods

    I know JB has solved this for you already but just thought I'd take the opportunity to show an alternative IF-less method:

    =MIN(C1,TODAY())-A1

    Based on the logic behind the question this assumes Completion Date would always be <= Current Date
    Last edited by DonkeyOte; 03-17-2009 at 04:30 AM.
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  6. 03-17-2009, 06:19 AM #6
    Joe B
    Joe B is offline
    Registered User
    Join Date
    03-16-2009
    Location
    Lusby, Maryland
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Calculating Number of Days for job length periods

    Quote Originally Posted by DonkeyOte View Post
    I know JB has solved this for you already but just thought I'd take the opportunity to show an alternative IF-less method:

    =MIN(C1,TODAY())-A1

    Based on the logic behind the question this assumes Completion Date would always be <= Current Date
    Correct and Thanks, that worked also
    Reply With QuoteRegister To Reply
  7. 03-17-2009, 06:43 AM #7
    Joe B
    Joe B is offline
    Registered User
    Join Date
    03-16-2009
    Location
    Lusby, Maryland
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Calculating Number of Days for job length periods

    I started applying the formulas and I realized that if I'm entering a new job order and I don't have an "in work" date yet, D1 returns a 39889 number. Is there a way to have it not display a value until a "in work" date is entered?
    Reply With QuoteRegister To Reply
  8. 03-17-2009, 06:49 AM #8
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculating Number of Days for job length periods

    Using mine

    Return Null: =IF(A1,MIN(C1,TODAY())-A1,"")
    Return Zero: =IF(A1,MIN(C1,TODAY())-A1,A1)

    Using JBs

    Return Null: =IF(A1,IF(C1,C1-A1,TODAY()-A1),"")
    Return Zero: =IF(A1,IF(C1,C1-A1,TODAY()-A1),A1)
    Reply With QuoteRegister To Reply
  9. 03-17-2009, 07:11 AM #9
    Joe B
    Joe B is offline
    Registered User
    Join Date
    03-16-2009
    Location
    Lusby, Maryland
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Calculating Number of Days for job length periods

    Thanks Donkey for another quick response... Works great!!!
    Reply With QuoteRegister To Reply
  10. 03-17-2009, 07:43 AM #10
    Joe B
    Joe B is offline
    Registered User
    Join Date
    03-16-2009
    Location
    Lusby, Maryland
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Calculating Number of Days for job length periods

    Quote Originally Posted by DonkeyOte View Post
    Using mine

    Return Null: =IF(A1,MIN(C1,TODAY())-A1,"")
    Return Zero: =IF(A1,MIN(C1,TODAY())-A1,A1)

    Using JBs

    Return Null: =IF(A1,IF(C1,C1-A1,TODAY()-A1),"")
    Return Zero: =IF(A1,IF(C1,C1-A1,TODAY()-A1),A1)
    I also added a +1 so that the value wouldn't return a zero for work being completed on the same day as received. At this point I don't have to track hours per task so it won't get anymore complicated.

    =IF(A1,MIN(C1,TODAY())-A1+1,"")
    Reply With QuoteRegister To Reply
  11. 03-17-2009, 10:19 AM #11
    Joe B
    Joe B is offline
    Registered User
    Join Date
    03-16-2009
    Location
    Lusby, Maryland
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Calculating Number of Days for job length periods

    I've created the conditional formatting based on the formulas results and now I have another problem. Please see this post
    http://www.excelforum.com/excel-work...-attached.html
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1