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

megera716

Board Regular
Joined
Jan 3, 2013
Messages
103
Office Version
  1. 365
Platform
  1. Windows
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/20202019DecemberJan-2020 or 1/1/2020
2/4/20202020JanuaryFeb-2020 or 2/1/2020
2/4/2020
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,281
Office Version
  1. 365
Platform
  1. Windows
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

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,281
Office Version
  1. 365
Platform
  1. Windows
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

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

ADVERTISEMENT

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

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,281
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 3, 2013
Messages
103
Office Version
  1. 365
Platform
  1. Windows
That's okay. I will go with the dummy date. Thank you so much for your help!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,225
Messages
5,600,406
Members
414,383
Latest member
kevinlarey

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