Due Date: Automatic Input using formula or function

**DianaEve** · 05-10-2013, 07:22 PM

Making a spreadsheet; in column c are lab dates in column d are dates 90 days from lab date (due dates). I used a formula: =c4+90 which works except if there's no date in column c then 03/31/1904(default date) appears in column d's cells. I need the formula to work only IF there's a date typed into column c, otherwise I need column d's cells to remain blank. Please advise! Thanks

**FDibbins** · 05-10-2013, 07:32 PM

Hi and welcome to the forum

Try this...
=if(C4="","",c4+90)

**DianaEve** · 05-10-2013, 07:43 PM

Sorry, maybe it's late and my eyes are blurry but what is that after =if(C4= ?,?, c4+90)? (self taught excel user :-( )

**FDibbins** · 05-10-2013, 07:51 PM

Self-taught too

Instead of just =c4+90 in D4, use this instead...
=if(C4="","",c4+90) there is nothing after it, just enter it like that

It says...if C4 is blank, leave D4 blank, else add 90 to the value in C4

**Tony Valko** · 05-10-2013, 07:52 PM

2 double quotes.

Replace each of your ? question marks with 2 double quotes.

**DianaEve** · 05-14-2013, 01:45 PM

Originally Posted by FDibbins

Hi and welcome to the forum

Try this...
=if(C4="","",c4+90)

Thanks, that worked well. Now how can I format the due dates that were figured by this formula to be red & bold if the date is today or anytime earlier? (this to show that it's past due)

**Tony Valko** · 05-14-2013, 03:55 PM

You can do that using conditional formatting.

Let's assume you want to apply this to cell A1.

Select cell A1

Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format

Enter this formula in the box below:

=A1<=TODAY()

Click the Format button
Select the desired style(s)
OK out

**DianaEve** · 05-15-2013, 11:56 AM

Why thank you! Now I need to change the font color if the date in C5(the due date) is within 30 days(before) of today. Can you help?

**FDibbins** · 05-15-2013, 01:55 PM

Use Tony's method, create a new rule and adjust the formula references...
=C5<=today()-30

**DianaEve** · 05-15-2013, 02:22 PM

Originally Posted by FDibbins

Use Tony's method, create a new rule and adjust the formula references...
=C5<=today()-30

That only works for today minus 30 days which is April 15th. I need it to format 30 days from the due date and all other days up to the due date. So if the due date was May 15th then the font would be formatted from April 15th to May 14th with a color that represents: it's time to do labwork. Thanks.