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

Board Regular
Joined
Jan 3, 2013
Messages
103
Office Version
  1. 365
Platform
  1. Windows
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"))
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
801
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 27, 2010
Messages
801
Office Version
  1. 365
Platform
  1. Windows
I have tried to set something up that looks like what you describe but I'm not having the same problem.
 

megera716

Board Regular
Joined
Jan 3, 2013
Messages
103
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 3, 2013
Messages
103
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
TypeDateNumNameItemAccountAmountYearMonthDeliveredRev Rec Date
Invoice4383116775RedactedRedactedCollaborative 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))
Invoice4383116775RedactedRedactedEvents 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))
Invoice4383116978RRedactedRedactedSoftware 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))
Invoice4383116978RRedactedRedactedKPI 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))
Invoice4383116978RRedactedRedactedReports 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))
Invoice4383117017RedactedRedactedOther 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))
Invoice4383117018RedactedRedactedReports 1500020192=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))
Invoice4383117019RedactedRedactedServices 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))
Invoice4383117019RedactedRedactedSoftware 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))
Invoice4383117019RedactedRedactedKPI 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))
Invoice4383117019RedactedRedactedReports 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))
Invoice4383117023RedactedRedactedOther Custom37502019August=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,364
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 3, 2013
Messages
103
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jan 3, 2013
Messages
103
Office Version
  1. 365
Platform
  1. Windows
Ah, I see you suggested a couple options. Will do in the morning :)
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
801
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,963
Messages
5,621,855
Members
415,862
Latest member
nascaline

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
Top