SOLVED

Billing Date

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3173092%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EBilling%20Date%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3173092%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20have%20a%20scenario%2C%20for%20any%20date%20less%20than%2020th%20of%20the%20month%2C%20i%20want%20excel%20to%20show%2020th%20of%20the%20current%20month%20e.g.%20if%20activity%20date%20is%207th%20of%20Jan%202022%2C%20I%20need%20excel%20to%20display%2020th%20of%20Jan%202022%20as%20billing%20date...%20Whereas%2C%20if%20activity%20date%20is%2021st%20of%20Jan%202022%2C%20I%20need%20excel%20to%20show%2020th%20of%20Feb%202022%20as%20billing%20date...%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ENeed%20to%20use%20this%20in%20SUMIFS%20so%20need%20shortest%20possible%20formula...%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ERegards%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3173092%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
New 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 (New 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...