Changing content of a cell once the current date has passed?

[xerxers]

Hi there,

Could you please assist me.

I work in a department with approx 30 people. I am trying to put together a spreadsheet that will keek track of vacation and various other leave days. Each of these leave days has been given a single alpha code ie V=vacation etc. This spreadsheet also shows RDO's. I have used Conditional formatting so that when a V or P is entered the background changes color. I have also setup a count of the days used which also shows days remaining.
At this time, for next year ALL of the vacation etc is potential, in other words it hasn't happened as yet but is planned.
What I would like to be able to do is once the date of the vaction has come and gone to change that V to VU (Vacation Used) automatically. So that when I look at the actual count per person I can tell instantly What they have used what they have planned and what their remaing Vaction will be.

I am sorry if I have sounded long winded but I wanted to ensure that I explained myself.

I do not know anything about VBA.

If you would like me to forward a copy of the worksheet please let me know.

Thank you.

Rob Mc

 

[colbymack]

Is it possible for you to insert another row (or column, depending on how you have your data presented) to evaluate the V's.
something like this:
If a cell has a V and the Vacation Date is < current date, then V, else VU

colbymack

 

[cfree36]

Column B = Date
Column C = Vacation Qualifier (V or P)
Column E = formula

=IF(C7="","",IF(B7<=TODAY(),C7&"U",C7))

Run you count off of column E (hide column E)

Although this does not change the cell... it will update your summary accordingly.

 

[xerxers]

Cell contents

Man...

What quick responses.

Thank you very much. I will try both suggestions and see which is the easiest to implement.

Once agin thank you

Rob Mc

 

[cfree36]

Both posts are basically the same... colbymack just types faster.

 

[xerxers]

cell changes

Very good.....

That made me chortle.....

Rob Mc

 

[cfree36]

Just for kicks... here is code that will do the trick.

This is placed in the worksheet module and runs when the worksheet is selected.
If you right click the worksheet and click view code.... you can paste this in.

Change the set range to equal the range where your dates are... the offset looks directly to the right for the V or P... so if dates are in column C the column to enter the time off qualifier would be column D or change the offset (0,1) to how many columns over from the date to look.
Add additional else if statements for other qualifiers....

ElseIf c.Offset(0, 1) = "P" Then
c.Offset(0, 1).Value = "PU"

Option Compare Text

Private Sub Worksheet_Activate()
Dim MyRange As Range
Set MyRange = Range("C6:C13")

For Each c In MyRange
    If c <= Now() Then
        
        If c.Offset(0, 1) = "V" Then
            c.Offset(0, 1).Value = "VU"
        ElseIf c.Offset(0, 1) = "P" Then
            c.Offset(0, 1).Value = "PU"
        End If
    End If
    Next c


End Sub

 

[xerxers]

Cell changes

cfree,

Thank you for the code.

This will tell you just how little I really know about Excel!!!!!!!!!!!

I have clicked on thw wroksheet but how do I view 'view code"........

Rob Mc

 

[colbymack]

Right click on the worksheet title . . . the popup menu that appears should give you a "view code" option.