Feb 16 2022 05:57 AM
Hi;
I have a scenario, for any date less than 20th of the month, i want excel to show 20th of the current month e.g. if activity date is 7th of Jan 2022, I need excel to display 20th of Jan 2022 as billing date... Whereas, if activity date is 21st of Jan 2022, I need excel to show 20th of Feb 2022 as billing date...
Need to use this in SUMIFS so need shortest possible formula...
Regards
Feb 16 2022 06:09 AM
SolutionLet's say activity date is in A2.
The billing date is
=DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>20),20)
If you use comma as decimal separator, use semicolon in the formula:
=DATE(YEAR(A2);MONTH(A2)+(DAY(A2)>20);20)
The formula can be filled down if required.
Feb 16 2022 06:18 AM
Feb 16 2022 06:09 AM
SolutionLet's say activity date is in A2.
The billing date is
=DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>20),20)
If you use comma as decimal separator, use semicolon in the formula:
=DATE(YEAR(A2);MONTH(A2)+(DAY(A2)>20);20)
The formula can be filled down if required.