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

asked on

Excel Date

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,
Avatar of ChloesDad
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
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


Thank you.
Avatar of byundt
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: