Need help with date and if statement newbie [Solved/Closed]

Report
-
cmarzahn
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3989 users have said thank you to us this month

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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3989 users have said thank you to us this month

RayH
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)
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

You're right, much tighter.
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.