# Calculate the number of days between dates

#### bdav1216

##### New Member
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 -

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Fluff

##### MrExcel MVP, Moderator
=IFERROR(--DATEDIF(DATE(LEFT(A2,4),MID(A2,6,2),1),TODAY(),"m"),"-")
and
=IF(SIGN(C2-TODAY())=-1,"-",C2-TODAY())

#### bdav1216

##### New Member
=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.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

#### bdav1216

##### New Member
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")

#### Fluff

##### MrExcel MVP, Moderator
If you just want the month you can use
=IFERROR(TEXT(DATE(2019,RIGHT(A2,2),1),"mmmm"),"-")

#### bdav1216

##### New Member
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!

Last edited: