30, 60, 90 between two dates

[ExcelEliterate]

Morning,

This seems like it would be fairly easy to do but as you can see by my username, i'm excel illiterate. So I have two columns B2 (10/01/2019) and C2 (01/22/2019) and I need to figure out what formula to use in order to make these reflect 30, 60, and 90 days. B2 has various dates but C2 is like a due date. I cannot figure it out. Any help would be most appreciated.



Thank you.

 

[Gerald Higgins]

Hi, welcome to the board.

What do you mean by "... make these reflect 30, 60, and 90 days" ?

Do you mean calculate the date 30 days after (or perhaps before) your dates ?
If yes, try

=B2+30

which should give you the date 30 days after the date in B2.

You can probably work out the other variations.

 

[gaz_chops]

Welcome to Mr Excel, not sure what you are after, does "make these reflect 30, 60, and 90 days" mean you want to add those days to each of the dates?

Maybe post an example of what results you are looking for.

 

[ExcelEliterate]

And I can't even speak excel...I think what I'm looking for is how to show that the various dates are 30 days past the due date, 60, 90 etc. using color. I've been told to use conditional formatting for this but I do not know what formula I would use. I'm sorry, new to excel.

 

[Gerald Higgins]

Yeah this is doable, but forget about Excel for a minute, just describe what you want to do, without thinking about doing it in Excel specifically.

You mentioned 2 dates - what is their significance ?

For example, you might want to colour one date red, if it was more than 30 days before today's date.
Or maybe you want to calculate the difference between the two dates you already have, and if that is more than 30 days, colour it red.

All of this is do-able, you just need to be clear about what you want.

Don't worry about describing the real world application of your data, that's probably not too important.
Just focus on what data you have, and be as clear as possible about what exactly you want to do with it.

 

[joeu2004]

I have two columns B2 (10/01/2019) and C2 (01/22/2019) and I need to figure out what formula to use in order to make these reflect 30, 60, and 90 days. B2 has various dates but C2 is like a due date.

And I can't even speak excel [...] I've been told to use conditional formatting

You certainly could be clearer about your requirements. You don't have to "speak Excel". But I think you might want something like the following.

First, the formula =B2-C2 gives the number of days between dates. You might set up Conditional Formats that will fill B2 with red, orange, or yellow if it s 90 or more days, 60 or more days, or 30 or more days after the date in C2. Here is one way.

1. Select B2.

2. Click Home > Condition Formatting > Manage Rules.

3. Click New Rule > Use A Formula..., and enter the formula =B2-C2 >= 30 into the field labeled "Format values where...". Then click Format > Fill, select the color (yellow), click OK > OK, and select "Stop If True".

4. Repeat #3 two more times, replacing 30 with 60 and yellow with orange, then replacing 30 with 90 and yellow with red in the instructions.

5. Finally, click OK to exit the Conditional Formatting Rules Manager.

The order of the rules is important. If you follow the above instructions exactly, the order should be correct: >=90, >=60, then >=30. If not, select each rule and click the up-arrow or down-arrow to correct the order as explained.

Test the conditional formatting by entering the following dates into B2, with 1/22/2019 in C2: 2/20/2019 (less than 30 days; no fill), 2/21/2019 (30 days; yellow fill), 3/22/2019 (less than 60 days; still yellow fill), 3/23/2019 (60 days; orange fill), 4/21/2019 (less than 90 days; still orange fill), 4/22/2019 (90 days; red fill), 4/23/2019 (more than 90 days; still red fill).

You can use the Format Painter to copy the format to other cells in column B. Select B2, click Format Painter, then select the cells in column B.