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"))

 

[6StringJazzer]

What is the heading for the column this formula in?

I see you have columns for Date, Year, and Month. What is the relationship among these? Are any of them formulas that refer to the others? If so what are the formulas?

I am not sure what the problem is and I don't know enough about your data to reconstruct your table and data. But the first thing I would try is to use DATE instead of DATEVALUE. The use of DATEVALUE looks correct to me, but when I see an error I look where I can try something different.

=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([@Year],[@Month],1))),DATEVALUE("12/31/2099"))

 

[6StringJazzer]

I have tried to set something up that looks like what you describe but I'm not having the same problem.

 

[megera716]

What is the heading for the column this formula in?

I see you have columns for Date, Year, and Month. What is the relationship among these? Are any of them formulas that refer to the others? If so what are the formulas?

I am not sure what the problem is and I don't know enough about your data to reconstruct your table and data. But the first thing I would try is to use DATE instead of DATEVALUE. The use of DATEVALUE looks correct to me, but when I see an error I look where I can try something different.

=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([@Year],[@Month],1))),DATEVALUE("12/31/2099"))

Hi 6string, I'll try anything! Responses to your questions:

The name of the column where this formula resides is "Rev Rec Date" (meaning, Revenue Recognition). The Account, Date, Year and Month columns are all hard-coded numbers out of our accounting system, no formulas. Date = Invoice Date. Account = Revenue account for the line item on the invoice. Year and Month = Year and Month of completion for that particular item. We pre-sell a lot of items and then recognize the revenue when we are done making it, so to speak (it's a digital product, not a physical one).

When I plugged in your substitution for DATEVALUE, it says I have entered too few arguments for this function. I assumed this is where you meant DATE instead so I changed it to that. Success - almost!

The formula works on rows where the Year column is blank, but now it returns a "#VALUE!" error on rows where there is a Year and Month. This appears to be because the Month column uses their "full" names (January, February, etc.). I changed one row with February in the Month column to a "2" and now it works. Can I convert the month name to a number within the formula?

 

[megera716]

Here's a selection of the data with identifying information removed. I turned on Show Formulas so you can see what references what. Turning that on also converted all of the normal dates in the Date column to numeric values but hopefully you get the idea. The last row with August in the month returns an error. The row with a 2 in the Month doesn't.

Type	Date	Num	Name	Item	Account	Amount	Year	Month	Delivered	Rev Rec Date
Invoice	43831	16775	Redacted	Redacted	Collaborative Subscriptions	26100			=IF(OR(ISNUMBER(SEARCH("Subscriptions",Sales[@Account])),Sales[@Year]<>""),"Delivered","Not Yet")	=OR(ISNUMBER(SEARCH("Custom",Sales[@Account])),ISNUMBER(SEARCH("Reports",Sales[@Account])),ISNUMBER(SEARCH("Events",Sales[@Account]))) Sales[@Year]="" DATEVALUE("12/31/2099") MAX(Sales[@Date]-DAY(Sales[@Date])+1,DATE(Sales[@Year],Sales[@Month],1))
Invoice	43831	16775	Redacted	Redacted	Events	3900			=IF(OR(ISNUMBER(SEARCH("Subscriptions",Sales[@Account])),Sales[@Year]<>""),"Delivered","Not Yet")	=OR(ISNUMBER(SEARCH("Custom",Sales[@Account])),ISNUMBER(SEARCH("Reports",Sales[@Account])),ISNUMBER(SEARCH("Events",Sales[@Account]))) Sales[@Year]="" DATEVALUE("12/31/2099") MAX(Sales[@Date]-DAY(Sales[@Date])+1,DATE(Sales[@Year],Sales[@Month],1))
Invoice	43831	16978R	Redacted	Redacted	Software Subscriptions	21750			=IF(OR(ISNUMBER(SEARCH("Subscriptions",Sales[@Account])),Sales[@Year]<>""),"Delivered","Not Yet")	=OR(ISNUMBER(SEARCH("Custom",Sales[@Account])),ISNUMBER(SEARCH("Reports",Sales[@Account])),ISNUMBER(SEARCH("Events",Sales[@Account]))) Sales[@Year]="" DATEVALUE("12/31/2099") MAX(Sales[@Date]-DAY(Sales[@Date])+1,DATE(Sales[@Year],Sales[@Month],1))
Invoice	43831	16978R	Redacted	Redacted	KPI Custom	21750			=IF(OR(ISNUMBER(SEARCH("Subscriptions",Sales[@Account])),Sales[@Year]<>""),"Delivered","Not Yet")	=OR(ISNUMBER(SEARCH("Custom",Sales[@Account])),ISNUMBER(SEARCH("Reports",Sales[@Account])),ISNUMBER(SEARCH("Events",Sales[@Account]))) Sales[@Year]="" DATEVALUE("12/31/2099") MAX(Sales[@Date]-DAY(Sales[@Date])+1,DATE(Sales[@Year],Sales[@Month],1))
Invoice	43831	16978R	Redacted	Redacted	Reports	13500			=IF(OR(ISNUMBER(SEARCH("Subscriptions",Sales[@Account])),Sales[@Year]<>""),"Delivered","Not Yet")	=OR(ISNUMBER(SEARCH("Custom",Sales[@Account])),ISNUMBER(SEARCH("Reports",Sales[@Account])),ISNUMBER(SEARCH("Events",Sales[@Account]))) Sales[@Year]="" DATEVALUE("12/31/2099") MAX(Sales[@Date]-DAY(Sales[@Date])+1,DATE(Sales[@Year],Sales[@Month],1))
Invoice	43831	17017	Redacted	Redacted	Other Subscriptions	13000			=IF(OR(ISNUMBER(SEARCH("Subscriptions",Sales[@Account])),Sales[@Year]<>""),"Delivered","Not Yet")	=OR(ISNUMBER(SEARCH("Custom",Sales[@Account])),ISNUMBER(SEARCH("Reports",Sales[@Account])),ISNUMBER(SEARCH("Events",Sales[@Account]))) Sales[@Year]="" DATEVALUE("12/31/2099") MAX(Sales[@Date]-DAY(Sales[@Date])+1,DATE(Sales[@Year],Sales[@Month],1))
Invoice	43831	17018	Redacted	Redacted	Reports	15000	2019	2	=IF(OR(ISNUMBER(SEARCH("Subscriptions",Sales[@Account])),Sales[@Year]<>""),"Delivered","Not Yet")	=OR(ISNUMBER(SEARCH("Custom",Sales[@Account])),ISNUMBER(SEARCH("Reports",Sales[@Account])),ISNUMBER(SEARCH("Events",Sales[@Account]))) Sales[@Year]="" DATEVALUE("12/31/2099") MAX(Sales[@Date]-DAY(Sales[@Date])+1,DATE(Sales[@Year],Sales[@Month],1))
Invoice	43831	17019	Redacted	Redacted	Services Subscriptions	30000			=IF(OR(ISNUMBER(SEARCH("Subscriptions",Sales[@Account])),Sales[@Year]<>""),"Delivered","Not Yet")	=OR(ISNUMBER(SEARCH("Custom",Sales[@Account])),ISNUMBER(SEARCH("Reports",Sales[@Account])),ISNUMBER(SEARCH("Events",Sales[@Account]))) Sales[@Year]="" DATEVALUE("12/31/2099") MAX(Sales[@Date]-DAY(Sales[@Date])+1,DATE(Sales[@Year],Sales[@Month],1))
Invoice	43831	17019	Redacted	Redacted	Software Subscriptions	180000			=IF(OR(ISNUMBER(SEARCH("Subscriptions",Sales[@Account])),Sales[@Year]<>""),"Delivered","Not Yet")	=OR(ISNUMBER(SEARCH("Custom",Sales[@Account])),ISNUMBER(SEARCH("Reports",Sales[@Account])),ISNUMBER(SEARCH("Events",Sales[@Account]))) Sales[@Year]="" DATEVALUE("12/31/2099") MAX(Sales[@Date]-DAY(Sales[@Date])+1,DATE(Sales[@Year],Sales[@Month],1))
Invoice	43831	17019	Redacted	Redacted	KPI Custom	25000			=IF(OR(ISNUMBER(SEARCH("Subscriptions",Sales[@Account])),Sales[@Year]<>""),"Delivered","Not Yet")	=OR(ISNUMBER(SEARCH("Custom",Sales[@Account])),ISNUMBER(SEARCH("Reports",Sales[@Account])),ISNUMBER(SEARCH("Events",Sales[@Account]))) Sales[@Year]="" DATEVALUE("12/31/2099") MAX(Sales[@Date]-DAY(Sales[@Date])+1,DATE(Sales[@Year],Sales[@Month],1))
Invoice	43831	17019	Redacted	Redacted	Reports	12500			=IF(OR(ISNUMBER(SEARCH("Subscriptions",Sales[@Account])),Sales[@Year]<>""),"Delivered","Not Yet")	=OR(ISNUMBER(SEARCH("Custom",Sales[@Account])),ISNUMBER(SEARCH("Reports",Sales[@Account])),ISNUMBER(SEARCH("Events",Sales[@Account]))) Sales[@Year]="" DATEVALUE("12/31/2099") MAX(Sales[@Date]-DAY(Sales[@Date])+1,DATE(Sales[@Year],Sales[@Month],1))
Invoice	43831	17023	Redacted	Redacted	Other Custom	3750	2019	August	=IF(OR(ISNUMBER(SEARCH("Subscriptions",Sales[@Account])),Sales[@Year]<>""),"Delivered","Not Yet")	=OR(ISNUMBER(SEARCH("Custom",Sales[@Account])),ISNUMBER(SEARCH("Reports",Sales[@Account])),ISNUMBER(SEARCH("Events",Sales[@Account]))) Sales[@Year]="" DATEVALUE("12/31/2099") MAX(Sales[@Date]-DAY(Sales[@Date])+1,DATE(Sales[@Year],Sales[@Month],1))

 

[Peter_SSs]

Any chance you could upload (to DropBox or OneDrive etc) a simplified (& sanitised) version of the file that contains all the tables & demonstrates the problem & share a link here?.

Is is hard to reconstruct formal tables, table names, pivot tables etc from limited information.

 

[megera716]

Any chance you could upload (to DropBox or OneDrive etc) a simplified (& sanitised) version of the file that contains all the tables & demonstrates the problem & share a link here?.

Is is hard to reconstruct formal tables, table names, pivot tables etc from limited information.

YES! I didn’t even know that was possible. How do I do that?

 

[megera716]

Ah, I see you suggested a couple options. Will do in the morning

 

[Peter_SSs]

Ah, I see you suggested a couple options. Will do in the morning

Cheers.

 

[6StringJazzer]

The last row with August in the month returns an error. The row with a 2 in the Month doesn't.

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.