Calculate the number of days between dates

bdav1216

New Member
Joined
Mar 19, 2016
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
How about
=IFERROR(--DATEDIF(DATE(LEFT(A2,4),MID(A2,6,2),1),TODAY(),"m"),"-")
and
=IF(SIGN(C2-TODAY())=-1,"-",C2-TODAY())
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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")
 
Upvote 0
If you just want the month you can use
=IFERROR(TEXT(DATE(2019,RIGHT(A2,2),1),"mmmm"),"-")
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top