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

megera716

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

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
803
Office Version
  1. 365
Platform
  1. Windows
megera716=Book1.xlsx
ABCDEFGHIJ
1TypeDateNumNameItemAccountAmountMonth YearDeliveredRec Date
2Invoice1/1/202016775RedactedRedactedCollaborative Subscriptions26100Delivered12/31/2099
3Invoice1/1/202016775RedactedRedactedEvents3900Not Yet12/31/2099
4Invoice1/1/202016978RRedactedRedactedSoftware Subscriptions21750Delivered12/31/2099
5Invoice1/1/202016978RRedactedRedactedKPI Custom21750Not Yet12/31/2099
6Invoice1/1/202016978RRedactedRedactedReports13500Not Yet12/31/2099
7Invoice1/1/202017017RedactedRedactedOther Subscriptions13000Delivered12/31/2099
8Invoice1/1/202017018RedactedRedactedReports15000February 2019Delivered1/1/2020
9Invoice1/1/202017019RedactedRedactedServices Subscriptions30000Delivered12/31/2099
10Invoice1/1/202017019RedactedRedactedSoftware Subscriptions180000Delivered12/31/2099
11Invoice1/1/202017019RedactedRedactedKPI Custom25000Not Yet12/31/2099
12Invoice1/1/202017019RedactedRedactedReports12500Not Yet12/31/2099
13Invoice1/1/202017023RedactedRedactedOther Custom3750August 2019Delivered1/1/2020
Sheet1
Cell Formulas
RangeFormula
I2:I13I2=IF(OR(ISNUMBER(SEARCH("Subscriptions",[@Account])),[@[Month Year]]<>""),"Delivered","Not Yet")
J2:J13J2=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

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1MonthYearFirst of Month
2520201-May-2020
3820191-August-2019
4August20191-August-2019
DATEVALUE
Cell Formulas
RangeFormula
C2:C4C2=DATEVALUE(1&"/"&[@Month]&"/"&[@Year])
 

6StringJazzer

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

Watch MrExcel Video

Forum statistics

Threads
1,126,994
Messages
5,622,053
Members
415,875
Latest member
Tarali

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