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