Date extraction question

[sitewolf]

It could be all my neurons simply aren't firing yet this morning, because I think this should be relatively simple but it's just not coming to me.

Each month I have a list of transactions, but because the (external so I can't change it) cutoff date isn't the first of the month, that list will always have some transactions from both the prior month and the current month. I want to keep the correct dates in my transaction log, but to upload into my accounting software I want dates to be all current month.

Example dates from my log:
9/27/22
9/29/22
10/4/22
10/7/22

What I want is an if statement where if a date is = or > than the 1st of the current month, it uses the correct date, but if it's < the 1st of the current month, it spits out the 1st of the current month.
In the above example, then, the formula would spit out 10/1/22 for the first 2. There's a journal entry date I'd be typing in to use as the current month, so I don't have to futz with that part of determining current.

 

[Georgiboy]

Maybe something like:

Book1
	B	C	D	E	F
1					Current Month
2					01/10/2022
3	27/09/2022	01/10/2022
4	29/09/2022	01/10/2022
5	04/10/2022	04/10/2022
6	07/10/2022	07/10/2022
Sheet1

Cell Formulas
Range	Formula
C3:C6	C3	=IF(MONTH(B3)<MONTH($F$2),$F$2,B3)

 

[sitewolf]

If that puts prior month dates to the 1st of the current month, it may be more elegant than what I just came up with
=IF(A1<F1-DAY(F1)+1,F1-DAY(F1)+1,A1) (where F1 is the control date)

Thanks!

 

[SpillerBD]

Or use MAX

 

[Georgiboy]

As long as you put the 1st day of the month in the Current Month cell then it will use that date for anything with a month less than that cell, if that makes sense?

 

[sitewolf]

It does, and that may be the simpler way to go....but the date I was wanting to use as my control date will generally be, for example, 10/27 (late in the month, the statement date)
BUT, I could use a formula to spit out 10/1 in that example and use THAT as my control date.

This is a credit card transaction log, so dates will always be either current month or a few from the prior month, because the statement cutoff is late in the month (i.e. the 27th)

 

[sitewolf]

Can you explain using Max here?

 

[SpillerBD]

Using GeorgiBoy's example, replacing the formula in C3.

=MAX(B3,$F$2)