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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

=DATE(YEAR(NOW()),MONTH(NO

=DATE(YEAR(NOW()),MONTH(NO

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(YEA

ASKER