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

1. ## 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 -  Reply With Quote

2. ## Re: Calculate the number of days between dates

=IFERROR(--DATEDIF(DATE(LEFT(A2,4),MID(A2,6,2),1),TODAY(),"m"),"-")
and
=IF(SIGN(C2-TODAY())=-1,"-",C2-TODAY())  Reply With Quote

3. ## Re: Calculate the number of days between dates Originally Posted by Fluff =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.  Reply With Quote

4. ## Re: Calculate the number of days between dates

You're welcome & thanks for the feedback  Reply With Quote

5. ## Re: Calculate the number of days between dates Originally Posted by bdav1216 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")  Reply With Quote

6. ## 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"),"-")  Reply With Quote

7. ## Re: Calculate the number of days between dates Originally Posted by Fluff 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).

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!  Reply With Quote

## User Tag List

#### Tags for this Thread

column, current, date, field, fields #### Posting Permissions

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