How about
=IFERROR(--DATEDIF(DATE(LEFT(A2,4),MID(A2,6,2),1),TODAY(),"m"),"-")
and
=IF(SIGN(C2-TODAY())=-1,"-",C2-TODAY())
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 -
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
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
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")
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
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.
Like this thread? Share it with others