Turn year & month into date, compare to another date, then return later of the two

[megera716]

We create widgets. When a widget is completed, we enter the month and year of completion in our accounting system. A customer may buy a widget before or after it's completed. We don't recognize the revenue until the later of the widget completion date or invoice date.

In a 4th column, I want to combine the year and month provided into a mm/dd/yy date, compare it to the date in the Invoice Date column, and return the month and year of the later of the two (it might be more clear to have it be the 1st day of that month).

The first two rows indicate that it was invoiced after the widget was completed so the revenue is recognized in the month of invoicing.

The 2/4/2020 row below would indicate the widget hasn't been completed as of invoicing since the Year and Month are blank. We will add it later and then the formula should return that date since obviously it will be after the invoice date.

Invoice Date (Column B)	Year of Widget Completion (Column M)	Month of Widget Completion (Column N)	Desired Result (Rev Recognition Date)
1/1/2020	2019	December	Jan-2020 or 1/1/2020
2/4/2020	2020	January	Feb-2020 or 2/1/2020
2/4/2020

 

[steve the fish]

This would work on my machine but im in UK and we have different dates but try it:

=MAX(B2-DAY(B2)+1,DATEVALUE(1&"-"&N2&"-"&M2))

 

[megera716]

This would work on my machine but im in UK and we have different dates but try it:

=MAX(B2-DAY(B2)+1,DATEVALUE(1&"-"&N2&"-"&M2))

HEY, it worked! Thank you!!

Next question -- I created a Pivot Table using these dates and I think it thinks the dates are text because it gives the option to sort A to Z instead of "Oldest to Newest." I formatted the cells as "short date" in the Number formatting and also ran Text to Columns to convert all to dates and still no luck. I can live with it if needed but if you have any ideas for how to make the Pivot read it as a date, I would be most appreciative!

 

[steve the fish]

Id have thought at least one of your values in the source data is textual. Turn off any formatting and look for alignment of the data. It should all be right aligned.

 

[megera716]

Well, I figured it out sort of. Could certainly use additional guidance. I'm using your marvelous formula as part of an IF statement to say if the account is X, Y or Z, run that formula and if it's not, then return blank (""). So I tried saying if it's X, Y or Z, run the formula, and if not, return DATEVALUE("12/31/2099").

Of course I now have a bunch of rows with a value of 12/31/2099. Is there a way to make it work with my original return "" if FALSE?

 

[megera716]

Ah, you posted as I was posting. Yes, the blank value would be text formatted. I'd prefer to have a blank if I can for FALSE values.

 

[steve the fish]

Im not sure there is. You could use the dummy date then filter it out maybe? If you have text in the column excel wont see it as numerical.

 

[megera716]

That's okay. I will go with the dummy date. Thank you so much for your help!!