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:51PM 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!
May 28, 2004 6:35PM PDT
- Collapse -
Thanks, but....
May 28, 2004 6:56PM PDT

I tried D4+28 but the result is a big number like 305678 (not exactly, I'm generalising) not a date! Any advice on that?
I'll follow the links you suggest for the other part but it looks out of my league! Actually though it wouldn't matter if it sent an e-mail every day.
Thanks, L Happy

- Collapse -
Re:Thanks, but....
May 28, 2004 9:28PM PDT

Saltrans,

1. A date is just a number (number of days since 1-1-1900 or so). Just give the cell the date format you like and it's displayed as a date. No magic here.

2. To send an email you will have to program in VBA, or find a ready-made piece of coding on the web. I've done in in MS Access, and I think it's possible with Excel also. But if you find formatting already difficult, I wonder if you will be able to do it yourself.

3. The conditional formatting should be in the reach of every Excel user. It's fully in the GUI. Study the help and the examples, and you'll succeed.

Kees

- Collapse -
Re:Re:Thanks, but....
May 29, 2004 1:00AM PDT

1. I see!!
2. You are absolutely right
3. I hope so & will persevere. I've never done more than autosum before & now I've been given a work project to create a complicated spreadsheet!!
Thanks for all your help. L Happy

- Collapse -
You may try an alternative for the conditional format.
May 29, 2004 1:29AM PDT

Add a colunmn behind the due date. Put an IF-formula in it that displays, say, a space if the date is less then today (use function NOW() to get it) and an exclamation mark if it is greater or equal (or any other criterion you define). Then filter on that column to see only the lines with a date overdue.
It's easy to record a macro to put the filter on and off.

Good luck with this second challenge.

And tell your boss to hire a professional for the automated e-mail. He (or she) will need one to three days, depending on the exact wishes for the content of the mail and the control of repeated mails. Such a programmer will charge 50 to 60 $ an hour, maybe.
Using a computer might enhance the productivity, but nobody ever said it came for free.

Kees