VBA or Formula

[ChrisFoster]

Hi all,

Bear with me here, I'm trying to explain everything I think is relevant.

I have circa 17,000 rows of data that contain revenue for my business, spanning from 22nd August 2022 to current date.
Each row has a date in column A stating the date funds hit the account.

However, for reporting reasons I need to break this down as if the months were 15th to 14th of each month, so for example 15th August to 14th September 2022 is actually classed as September 2022, once it hits 15th September 2022 that is the start of October 2022 up to 14th October which then which moves into November from the 15th onwards, etc.

Column B has the amounts in £'s.
Column C has the type of transaction this is.

How on earth can I do this without trawling through 17,000 cells to change the date?

Thanks

Chris

 

[RoryA]

You could add a column with a formula like:

=DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>14),1)

 

[Phuoc]

Or try this:

=EOMONTH(A2-14,0)+1

 

[ChrisFoster]

Or try this:

=EOMONTH(A2-14,0)+1

Thanks, this one worked perfectly.