Link to home
Start Free TrialLog in
Avatar of W.E.B
W.E.B

asked on

Excel Date

Hello,
Can you please help,

I need to have a formula in Cell A1

If Today's date > = 1st and < = 14th then
CELL A1 = 16th of Previous Month -- CELL B1 = Last day of Previous month.
If Today's date = 15th then
CELL A1 =  1st of Current Month -- CELL B1 = 15th of Current Month
If Today's date > = 16th and < = End of Month then
CELL A1 =  16th of Current month. -- CELL B1 = Last day of Current month.

not sure if this can be done in one formula,
if not, I'm OK with 2 formulas (CELL A1, B1)

Any help is appreciated,
thanks
ASKER CERTIFIED SOLUTION
Avatar of ChloesDad
ChloesDad
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of W.E.B
W.E.B

ASKER

Thank you.
Avatar of byundt
byundt
Flag of United States of America image
I can shorten the formulas, but they are still going to be hard to understand:
=DATE(YEAR(NOW()),MONTH(NOW())-(DAY(NOW())<15),1+15*(DAY(NOW())<>15))        for A1
=DATE(YEAR(NOW()),MONTH(NOW())+(DAY(NOW())>15),15*(DAY(NOW())=15))             for B1

When you are testing ChloesDad's suggestion for cell A1, I believe there was a typo in the last digit of the formula. I think he meant:
=IF(DAY(NOW())<15,DATE(YEAR(NOW()),MONTH(NOW())-1,16),IF(DAY(NOW())=15,DATE(YEAR(NOW()),MONTH(NOW()),1),DATE(YEAR(NOW()),MONTH(NOW()),16)))