SOLVED

Billing Date

Copper Contributor

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

2 Replies
best response confirmed by shahzad_afzal_pk (Copper Contributor)
Solution

@shahzad_afzal_pk 

Let'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.

Thanks for quick response and this really helped alot...
Grateful...
1 best response

Accepted Solutions
best response confirmed by shahzad_afzal_pk (Copper Contributor)
Solution

@shahzad_afzal_pk 

Let'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.

View solution in original post