[albelle]

Hi,
I'm trying to find a way to input information into an Excel spreadsheet that can "expire" or "fall off" the spreadsheet after 3 months automatically. Is this possible?

For example: if I'm keeping track of attendance records, and certain information should fall off the record after 3 months or 6 months, is there a way to set up a cell (not the entire spreadsheet or row or column, just the pertinent cell) to do this automatically? Please advise.

Thanks!

[JBeaucaire]

What happens to the cell after? New data input or it goes empty forever?

[JBeaucaire]

I could imagine a formula IN the cell that would do this, but it would make entering data a real bear. You compare today's date to some specific thing and then make the data appear or disappear based on that. For instance:

=IF((TODAY()-90)<=F1,"A","(blank)")

This assumes there is some expiration date listed in cell F1. When Today() (calculates today's date based on your computer clock) is greater than 90 days after the date in cell F1, the cell would stop showing "A" and show (blank) instead.

The reason this is a bear is that to enter a display value to show for the next 90 days, you would have to edit the "A" to show the data you want, then enter the expiration date in cell F1. I can't think of a way to enter the expiration date in the formula itself.

Either way, entering data in a cell with this formula would take some TLC all the time, could be irritating.

[albelle]

What happens to the cell after? New data input or it goes empty forever?

Goes empty forever

[albelle]

I could imagine a formula IN the cell that would do this, but it would make entering data a real bear. You compare today's date to some specific thing and then make the data appear or disappear based on that. For instance:

=IF((TODAY()-90)<=F1,"A","(blank)")

This assumes there is some expiration date listed in cell F1. When Today() (calculates today's date based on your computer clock) is greater than 90 days after the date in cell F1, the cell would stop showing "A" and show (blank) instead.

The reason this is a bear is that to enter a display value to show for the next 90 days, you would have to edit the "A" to show the data you want, then enter the expiration date in cell F1. I can't think of a way to enter the expiration date in the formula itself.

Either way, entering data in a cell with this formula would take some TLC all the time, could be irritating.

Wow, that sounds like more work than just deleting it manually after 30 days (assuming I can remember what day it needs to be deleted 3 months from now). Would it perhaps be possible to set this formula up on a macro that would utilize whatever data was put into the cell as "A". For instance, if I put "occurence" in the cell today, but the next date column's cell has "absence", could I run the macro to just accept whatever the contents of the cell is as "A" and automatically calculate to go blank in 90 days? Then Occurrence would disappear 3 months later, and absence would disappear the next day (which is 3 months later for that particular cell). Thank you for your response! :)

[JBeaucaire]

I read that 3 times and I'm still lost...

[JBeaucaire]

What data is IN the cell? A date? Is there a date anywhere in this row/column to give a macro or a script some sort of reference?

I'm stabbing in the dark here. If the columns/rows aren't dated and the data IN the cell has nothing referring to a date IN it, then a macro/script has nothing to work from to operate.

If this is some sort of school attendance thing, wouldn't the columns be dated? That would help.

I'm thinking you're going to need a macro that asks that simply deletes all entries prior than 90 days from today. The cool thing is that the macro can be set to run every time you open the sheet, so it's self-trimming.

But this macro would need reference points. Delete everything in columns dated 90 days before today? You get the idea.

[albelle]

What data is IN the cell? A date? Is there a date anywhere in this row/column to give a macro or a script some sort of reference?

I'm stabbing in the dark here. If the columns/rows aren't dated and the data IN the cell has nothing referring to a date IN it, then a macro/script has nothing to work from to operate.

If this is some sort of school attendance thing, wouldn't the columns be dated? That would help.

I'm thinking you're going to need a macro that asks that simply deletes all entries prior than 90 days from today. The cool thing is that the macro can be set to run every time you open the sheet, so it's self-trimming.

But this macro would need reference points. Delete everything in columns dated 90 days before today? You get the idea.

I'm so sorry for the confusion.. I was worried about that not making sense. I'll try to clarify! :)

The columns are dated. The actual cells would contain either "occurence", "absence", "present", or "tardy", etc. The thing is, all "occurences" would need to delete after 6 months, while absences and tardies would need to delete after 3 months. So, it'd have to be a smart macro in terms of skipping over the items that need to be there longer than 3 months. And of course the "present" entries should never be deleted at all. Is that too many variables? And a macro that runs automatically every time the spreadsheet is opened would be AWESOME. Thank you!

[JBeaucaire]

Shouldn't tardy convert to presence?

[albelle]

Shouldn't tardy convert to presence?

Actually, now that I think about it, yes, everything should convert to "present" after the appropriate length of time (3 months for absence/tardy, 6 months for occurrence). Good catch! :)

[ScottGem]

Frankly, I think you should be using a database and not Excel. This would be simple in Access. You would run a Delete or an Update query when you open the DB which would delete or update any records over a specific age the Age can depend on the contents of certain fields.

[JBeaucaire]

Absence shouldn't convert to presence, should it? That would be weird.

Occurrence -> presence
Tardy -> presence
Absence -> stays or disappears

?

[albelle]

Frankly, I think you should be using a database and not Excel. This would be simple in Access. You would run a Delete or an Update query when you open the DB which would delete or update any records over a specific age the Age can depend on the contents of certain fields.

That would make logical sense to use Access, but unfortunately I only have Word and Excel to work with for this situation. *sigh* Thank you for your response :)

[albelle]

Absence shouldn't convert to presence, should it? That would be weird.

Occurence -> presence
Tardy -> presence
Absence -> stays or disappears

??

I vote we start over. If I have a spreadsheet that lists a person's name on each row, and then a cell is populated with the date and type of occurrence (be it tardy, late, or absent). The Cell should go blank after once the date combined with the type of occurrence hits its "expiration". So if I have:

Row 1 [Person's Name] [8/3/08 Tardy]

I want the [8/3/08 Tardy] to go blank after 3 months.

If I have:
Row 1 [Person's Name] [8/3/08 Late]

I want the [8/3/08 Late] to go blank after 6 months.


Is there some type of macro that could search for all cells with "late" in it and check the date in that same cell, and know to delete the contents of that cell in 6 months?

[Depressed in MO]

Frankly, I think you should be using a database and not Excel. This would be simple in Access. You would run a Delete or an Update query when you open the DB which would delete or update any records over a specific age the Age can depend on the contents of certain fields.

I was thinking the same thing!

(couldn't use the agree/disgree option without spreading it around to others first!)

[ScottGem]

Do you want both the date and the reason to go blank? Or do you want the whole row deleted? Or just the reason deleted?

[albelle]

Do you want both the date and the reason to go blank? Or do you want the whole row deleted? Or just the reason deleted?

I want the whole cell (which has both the date and reason in it) to go blank.

[ScottGem]

Why would you put both the date and reason in the same cell? That's going to make it much more difficult to determine what the date of the record is.

Why would you want a row with just a name in it? That just makes no sense to me.

[JBeaucaire]

Isn't tardy and late the same thing?

[albelle]

Isn't tardy and late the same thing?

Ah well, for us, tardy means 1-14 min late. Late means 15 min to 4 hours late. Bizarre, I know. ;)