+ Reply to Thread
Results 1 to 8 of 8

Automating date difference calculator

  1. #1
    Registered User
    Join Date
    05-06-2008
    Posts
    3

    Automating date difference calculator

    Hi

    I need to know how to run a formula everytime I open my workbook. There is 3 columns A1, B1 and C1. A1 have start date, B1 end date and C1 have the formula "=datedif(a1,b1,"d")".

    I need this formula to run from first row until last the last row with an entry (or last row in workbook???) in it and update every "C" cell for me?

    Hope I make sense?

    Thank You

    Gelbert

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    =IF(A1="","",datedif(a1,b1,"d"))
    You can drag this the length of the column and it will only show for rows that have an entry.

  3. #3
    Registered User
    Join Date
    05-06-2008
    Posts
    3

    Question Not working

    Hi

    I'm a noob at this so bear with me. All the A & B columns do have a date in, meaning when the project was started (A) and ended (B). What C column must show me in days is how long I was busy with the project, see my pic below

    A B C
    1 1 May 5 May 4
    2 6 May 12 May 6
    3 13 May 16 May 3

    I need a formula, or macro, to do the C column automatically starting from C1 and ending in C999999, or last cell that have a value everytime the book is opened.

    Hope this explains it better?

    Gelbert

  4. #4
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    =IF(A1="","",B1-A1+1)
    This will take the number of days inclusive of the start and end dates. In other words, if you started on May 1 and ended May 2, it will return 2.

  5. #5
    Registered User
    Join Date
    03-01-2008
    Location
    New Jersey USA
    MS-Off Ver
    2007
    Posts
    32

    Error in formula

    Darkyam, I use your both formula in my sheet, but I get error, in your
    1st formula I enter start date cell show #NUM! ,
    2nd if enter start date " 2 May: cell show -39569

    Thank you to help others.

  6. #6
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    My fault, I didn't pay enough attention here to notice that the end date is mentioned first and the start date last.
    =IF(A1="","",A1-B1+1)

  7. #7
    Registered User
    Join Date
    03-01-2008
    Location
    New Jersey USA
    MS-Off Ver
    2007
    Posts
    32
    Hello Darkyam, Sorry to say its not working sorry,
    I try some change, now it is what I want. i.e

    =IF(B3>0,(B3-A3)+1,0)

    Thank you to helping others

  8. #8
    Registered User
    Join Date
    05-06-2008
    Posts
    3

    At last

    Hi

    Thanks so much, it works...

    This is my formula based on your replies

    =IF("Receive Date">0,("Start Date" - "End Date")+1,0)

    Thanks

    Gelbert

    PS Now how do I run this everytime I open my workbook?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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