Calculate the number of days between dates

bdav1216

New Member
Joined
Mar 19, 2016
Messages
22
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 -
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,132
Office Version
365
Platform
Windows
How about
=IFERROR(--DATEDIF(DATE(LEFT(A2,4),MID(A2,6,2),1),TODAY(),"m"),"-")
and
=IF(SIGN(C2-TODAY())=-1,"-",C2-TODAY())
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,132
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

bdav1216

New Member
Joined
Mar 19, 2016
Messages
22
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
Joined
Jun 12, 2014
Messages
30,132
Office Version
365
Platform
Windows
If you just want the month you can use
=IFERROR(TEXT(DATE(2019,RIGHT(A2,2),1),"mmmm"),"-")
 

bdav1216

New Member
Joined
Mar 19, 2016
Messages
22
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:

Forum statistics

Threads
1,077,678
Messages
5,335,630
Members
399,030
Latest member
bryanwa

Some videos you may like

This Week's Hot Topics

Top