Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel Formula Wanted!

May 28, 2004 4:15PM PDT

Can anyone tell me how to do this please?
I want a formula to make a cell automatically produce a date that is 28 days later than the date entered into the adjacent cell. Then, if the date is today or has already passed, the date should turn red. If it is possible I'd like it to generate an automatic e-mail to the person whose name appears in another adjacent cell but that isn't essential.
So, if 01/01/04 is in D4, 29/01/04 should be generated in E4 (I'm in Europe so use dd/mm/yy) and if it is now 30/01/04 then the date 29/01/04 should turn red.
Any clever people out there willing to help? TVM

Discussion is locked

- Collapse -
Re: Excel Formula Wanted!
Jun 2, 2004 3:41PM PDT

To enter a date 28 days later than a previous date, just add 28 to an existing date.

For example (and I just tried this):
In cell A2 enter a start date. I entered "Jan 1"
NO QUOTES! I only used them here to bracket the characters to enter.
(You can enter any format, Excel should be smart enough to convert it to your desired format. You can enter 1/1, or 1/1/04, or January-1, or all kinds of ways. If you don't get what you want then re-enter it a way that you know works).

Then in cell A3 just enter (again, not the quotes) "=A2+28"
Here again, Excel is smart enough to know you mean to work with dates, and it will add 28 days to A2.

Then just select cell A3 and grab the lower right handle, and drag it down to as many cells as you like. All those cells will automatically have the successive dates entered.
---------------
To get the dates past today (whatever that date is) to turn red:
First set up one cell, like cell B1, to hold the formula "=TODAY()"
that will automatically adjust to whatever today's date is.

Then select all cells with the dates and select the command: Format>Conditional Formatting.
In the window that opens use for the 3 condition fields:
"Cell Value Is" "less than" "=$B$1"
and set your format to be Red text.

That does your first two requests.

If you get that far, let me know and I'll see what I can do to explain the rest that you want.

- Collapse -
Re:Excel Formula Wanted!
Jun 2, 2004 3:45PM PDT

oops, sorry! I see in another thread, you asked the question a 2nd time, and you got an answer there.

Hope you got your desired results.

- Collapse -
Re:Re:Excel Formula Wanted!
Jun 4, 2004 1:56AM PDT

I'm very grateful really. The duplicate posting was a mistake as I refreshed the page. I'm grateful for all the help I can get. I'll try your answer tomorrow & report. TVM.