# 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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by