Date result from formula keeps returning as non-date format, I have to do a bunch of things to make it a date, very manual -- HELP

[megera716]

I got some excellent help here the other day with a formula and the formula works perfectly, but whenever I paste in new rows of data and drag the formula down, the value is not actually a date (as evidenced by my Pivot Table on this data asking me to sort A to Z instead of Oldest to Newest).

So back I go to my table, run text to columns, convert to dates, then my cell is just showing the text of the formula, I change number format to short date, find and replace = with = to make it re-calculate all the formulas. Is there a simpler way?

In "English", here is my formula: if the Account column contains "Custom", "Reports" or "Events" (the Account is a larger text string but it will have one of these words in it), then if the Widget Completion Year is blank, then return the date 12/31/2099. If Widget Completion Year is not blank, return the later of the Invoice Date or 1st of the month of the Widget Completion Year and Month. If the Account field doesn't contain one of those 3 words, return date 12/31/2099 (so I won't have blank values which are formatted as text and also causes the above issue with the Pivot).

=IF(OR(ISNUMBER(SEARCH("Custom",[@Account])),ISNUMBER(SEARCH("Reports",[@Account])),ISNUMBER(SEARCH("Events",[@Account]))),IF([@Year]="",DATEVALUE("12/31/2099"),MAX([@Date]-DAY([@Date])+1,DATEVALUE(1&"/"&[@Month]&"/"&[@Year]))),DATEVALUE("12/31/2099"))

 

[megera716]

That, of course, is the problem. Your formula has this bit:

DATEVALUE(1&"/"&[@Month]&"/"&[@Year])

which for the last row is being evaluated as
DATEVALUE("1/August/2019")
and of course Excel can't make sense of that.

In Excel one should always deal with dates as dates rather than words. The real solution is to enter 8 instead of "August."

IMHO an even better data design is to use a single column for a date, instead of separate columns for year and month. Then you would enter 8/1/2019 and you could display it using a "Mmmm yyyy" format. You would update your formula accordingly. Example is on the way.

For sure. The Year and Month was set up before my time and we have a lot of historical data using those fields so I don't want to abandon them now.

The EXCELLENT news is that your substitution of DATE for DATEVALUE seems to have fixed the problem! I'm converting the month name to a number in the middle of that DATE formula.
DATE([@Year],MONTH([@Month]&1),1)
When I inserted new table rows and pasted more raw data, it updated automatically and I didn't have to do all the reformatting song and dance like before ?

 

[6StringJazzer]

megera716=Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Type	Date	Num	Name	Item	Account	Amount	Month Year	Delivered	Rec Date
2	Invoice	1/1/2020	16775	Redacted	Redacted	Collaborative Subscriptions	26100		Delivered	12/31/2099
3	Invoice	1/1/2020	16775	Redacted	Redacted	Events	3900		Not Yet	12/31/2099
4	Invoice	1/1/2020	16978R	Redacted	Redacted	Software Subscriptions	21750		Delivered	12/31/2099
5	Invoice	1/1/2020	16978R	Redacted	Redacted	KPI Custom	21750		Not Yet	12/31/2099
6	Invoice	1/1/2020	16978R	Redacted	Redacted	Reports	13500		Not Yet	12/31/2099
7	Invoice	1/1/2020	17017	Redacted	Redacted	Other Subscriptions	13000		Delivered	12/31/2099
8	Invoice	1/1/2020	17018	Redacted	Redacted	Reports	15000	February 2019	Delivered	1/1/2020
9	Invoice	1/1/2020	17019	Redacted	Redacted	Services Subscriptions	30000		Delivered	12/31/2099
10	Invoice	1/1/2020	17019	Redacted	Redacted	Software Subscriptions	180000		Delivered	12/31/2099
11	Invoice	1/1/2020	17019	Redacted	Redacted	KPI Custom	25000		Not Yet	12/31/2099
12	Invoice	1/1/2020	17019	Redacted	Redacted	Reports	12500		Not Yet	12/31/2099
13	Invoice	1/1/2020	17023	Redacted	Redacted	Other Custom	3750	August 2019	Delivered	1/1/2020
Sheet1

Cell Formulas
Range	Formula
I2:I13	I2	=IF(OR(ISNUMBER(SEARCH("Subscriptions",[@Account])),[@[Month Year]]<>""),"Delivered","Not Yet")
J2:J13	J2	=IF(OR(ISNUMBER(SEARCH("Custom",[@Account])),ISNUMBER(SEARCH("Reports",[@Account])),ISNUMBER(SEARCH("Events",[@Account]))),IF([@[Month Year]]="",DATEVALUE("12/31/2099"),MAX([@Date]-DAY([@Date])+1,DATE(YEAR([@[Month Year]]),MONTH([@[Month Year]]),1))),DATEVALUE("12/31/2099"))

 

[6StringJazzer]

...your substitution of DATE for DATEVALUE seems to have fixed the problem! I'm converting the month name to a number in the middle of that DATE formula.
DATE([@Year],MONTH([@Month]&1),1)

I am not sure how that is converting a string to a month number but a happy face is a good face

 

[Peter_SSs]

Your formula has this bit:

DATEVALUE(1&"/"&[@Month]&"/"&[@Year])

which for the last row is being evaluated as
DATEVALUE("1/August/2019")
and of course Excel can't make sense of that.

In Excel one should always deal with dates as dates rather than words. The real solution is to enter 8 instead of "August."

Perhaps I am misunderstanding what you are getting at, but isn't dealing with dates as text exactly what DATEVALUE() is for (to turn them into 'real' dates)?

20 04 02.xlsm
	A	B	C
1	Month	Year	First of Month
2	5	2020	1-May-2020
3	8	2019	1-August-2019
4	August	2019	1-August-2019
DATEVALUE

Cell Formulas
Range	Formula
C2:C4	C2	=DATEVALUE(1&"/"&[@Month]&"/"&[@Year])

 

[6StringJazzer]

I have to retract that statement. Apparently Excel really does know what "1/August/2019" is supposed to mean. Normally I would expect DATEVALUE to accept only standard date formats but it seems to have some flexibility.

Nevertheless, in Excel you get better results by dealing with dates as date values rather than converting back and forth to text.