Need help with date and if statement newbie

Solved/Closed
T - Feb 18, 2015 at 03:16 AM
cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
- Feb 20, 2015 at 10:18 AM
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

RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
25
Feb 18, 2015 at 11:22 AM
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
cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
Feb 18, 2015 at 12:39 PM
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
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
25
Feb 18, 2015 at 05:47 PM
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
cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7 > RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016

Feb 20, 2015 at 10:18 AM
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