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