• Status: Solved
• Priority: Medium
• Security: Public
• Views: 166

# Column population based on date

i would like to have an X displayed in whatever column the expiration date listed in column B is versus todays date for each activity listed in column A.

The number in line 3 represent the days remaining before expiration of the license in the case of Auto registrations I filled in how I would like the sheet to work by plaing an X in the 365+ days reamining before license expiration.

If posible please give me a formula based solution and not a macro.

Thanks
Licensing.xlsx
0
wcody
1 Solution

Commented:
I think you need another category in there, assuming that 180 column shows dates between 180 and 365 then I think you need another column to show between 0 and 30 (marked as 0 on attached)....I'm assuming "expired" is any date in the past?

Sample formula for between 180 and 365

=IF(AND(B4-TODAY()<365,B4-TODAY()>=180),"X","")

regards, barry
Licensing-barry.xlsx
0

Excel VBA DeveloperCommented:
I agree with Barry's suggestion of a "0" aging column here.  However, I would also like to suggest that the formulas reference the actual aging values in order to allow this table to be dynamically adjusted.  It also makes the formulas "copy-able" and more similar.

For example, the formula to indicate between 180 and 365 would now look like this:

=IF(AND(\$B4-TODAY()<C\$3,\$B4-TODAY()>=D\$3),"X","")

And, a custom format can be used to allow the value 365 to appear like "365+"

-Glenn

Licensing-barry-mod.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.