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

Need help with date and if statement newbie
Fenix internet on bank statement - Guide
Application for bank statement sbi - Guide
Excel if statement - Guide
An if statement nested within another if statement will produce how many possible results? - Guide
Vba case statement with string - Guide

3 responses

Answer 1 / 3

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

Answer 2 / 3

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.

Answer 3 / 3

T
Feb 20, 2015 at 04:15 AM

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.

Similar discussions

Removing " false" value in "IF"

Need help to remove FALSE from cell

removing false in excel

Removing "FALSE" from an IF statement with a VLOOKUP

Similar discussions

Subscribe To Our Newsletter!