# 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?

## 3 responses

RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
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))
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)
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
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)
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.
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.