Need help with date and if statement newbie

Solved/Closed
Report
-
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
-
Hello,

I am trying to work out a formula if possible, to determine a date based on a date in another cell.
So if the Date in Cell A is 3/1/2015, I need Cell B to say 1/1/2015.

But the key is if Cell A's date is between day 1-15, Cell B should return 1st of current month. if Cell A's date is between day 16-31 (or last day of month) then Cell B's date should 1st of next month.

is this possible?

thanks, in advance


3 replies

Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
Assuming the date is in Cell A1, formula goes in B1:

=IF(AND(DAY(A1)>=1, DAY(A1)<=15),DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1)+1,1))
1
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
I believe this will do it.
(Where C3 is the target cell)
=IF(DAY(C3)<16,EOMONTH(C3,-1)+1,DATE(YEAR(C3),MONTH(C3)+1,0)+1)
1
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
Nice. I have never used the EOMONTH function before.

This version is tidier.
=IF(DAY(C3)<16,EOMONTH(C3,-1)+1,EOMONTH(C3,0)+1)
0
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7 >
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016

You're right, much tighter.
0
Perfect thanks guys, works really well... all of them. I wrote this formula.

=IF(DAY(E2)<16,DATE(YEAR(E2),MONTH(E2),1),DATE(YEAR(E2),MONTH(E2)+1,1))

thanks guys really appreciate the help.
0