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

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
Asked:
wcody
1 Solution
 
barry houdiniCommented:
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
 
Glenn RayExcel 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now