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
Microsoft Excel

Avatar of undefined
Last Comment
byundt

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
ChloesDad

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
W.E.B

ASKER
Thank you.
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:
=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)))
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes