Calculate the number of days between dates
Results 1 to 7 of 7

Thread: Calculate the number of days between dates
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculate the number of days between dates

    Question 1:
    Using the date field below, I would like to create a new field that subtracts today (current month/year) from this field's format below. There are some fields that do not have a date value ('-' or blank) - in those cases output a '-'.

    Current Formula:
    =1*DATEDIF(DATE(LEFT(A2,4),MID(A2,6,2),1),TODAY(),"m")
    Current Formula is working, but outputs #VALUE instead of '-' when there is no value. How would I go about updating/fixing that?
    Column A: Column B(Expected Value):
    2019-03 04
    2019-07 00
    2019-06 01
    2019-05 02
    2019-04 03
    - -

    Question 2:
    Using the date field below, how would I count the number of days between today and the date below? This field will have some fields with a blank value and some dates in Column C could be in the past. If dates are in the past or are missing so that the calculation is not logical, output a '-'.

    Column C Column D (Expected Value):
    07/22/2019 2
    07/01/2019 -
    07/20/2019 0
    07/19/2019 -

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,051
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Calculate the number of days between dates

    How about
    =IFERROR(--DATEDIF(DATE(LEFT(A2,4),MID(A2,6,2),1),TODAY(),"m"),"-")
    and
    =IF(SIGN(C2-TODAY())=-1,"-",C2-TODAY())
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Mar 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate the number of days between dates

    Quote Originally Posted by Fluff View Post
    How about
    =IFERROR(--DATEDIF(DATE(LEFT(A2,4),MID(A2,6,2),1),TODAY(),"m"),"-")
    and
    =IF(SIGN(C2-TODAY())=-1,"-",C2-TODAY())
    Thank you!
    That seems to be working I'll review today.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,051
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Calculate the number of days between dates

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Mar 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate the number of days between dates

    Quote Originally Posted by bdav1216 View Post
    Thank you!
    That seems to be working I'll review today.
    One last question. The formula is outputting a correct value (0-12 with 0 representing the current month of July, 1 being June, etc. and a '-' when the data cannot be calculated).

    Using that value, I created a new column to output the actual month. Doing this is translating the '0' to 'July' which is helpful.
    However, 2 questions
    1) Is this formula the best way to do this? I found this by searching on Google.
    2) How do I update this formula to get rid of the #VALUE when the value of formula that you created is '-'.


    =TEXT(DATE(2000,C1,1),"mmmm")

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,051
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Calculate the number of days between dates

    If you just want the month you can use
    =IFERROR(TEXT(DATE(2019,RIGHT(A2,2),1),"mmmm"),"-")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Mar 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate the number of days between dates

    Quote Originally Posted by Fluff View Post
    If you just want the month you can use
    =IFERROR(TEXT(DATE(2019,RIGHT(A2,2),1),"mmmm"),"-")
    Thank you for the recommendation, but I do need to create a new field (separate than the one you helped create) because for a report that I am creating, I need to first identify the 3 most recent months (0, 1 and 2) from the current month; however, for the purposes of the report, I want to display the month (by name, i.e. July, June or May).

    Edited to add:
    If I use your formula, that gives me the actual month name, and I can the original formula to give me the month since completed.

    I think I have what I need now!
    Last edited by bdav1216; Jul 20th, 2019 at 04:02 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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