How to add an automatic, static previous week ending date to a cell once an action is done using vba

[peta28]

Hi,

I want to add an automatic static week ending date for the previous week into a payroll template. Workers are paid for the previous week worked, they can work Monday to Sunday so I need the VBA to add the previous week ending date (which should be Sunday). Any time the grand total cells (H46, I46, O82,O113) have a figure greater than 0 I want the date cell to show the previous week ending date.

So if I do the payroll on Tuesday the date shown would be the previous Sunday.

I've tried this formula =IF(WEEKDAY(TODAY(),2)=7,TODAY(),TODAY()-WEEKDAY(TODAY(),2)) but it only gives me a volatile previous week ending date which means it's not static and it's not triggered by input into the the grand total cell so it comes up whenever the template opens.

any help with this problem would be greatly appreciated. Thanks

 

[AlphaFrog]

Would this work...

=IF(SUM(H46, I46, O82, O113)>0,TODAY()-WEEKDAY(TODAY(),1)+1,"")

 

[Smitty]

Welcome to the Board!

What cell/range do you want the date in?

If you're talking about manual entry triggering the entry you can use a Change event for when the Grand Total cell changes. Although if that changes as the result of a formula you'd need a Calculate event.

Here's are examples of both:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A1")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Value >= 1 <SPAN style="color:#00007F">Then</SPAN> Target.Offset(, 1).Value = <SPAN style="color:#00007F">Date</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>        <SPAN style="color:#00007F">Set</SPAN> rng = Range("A2")<br>        <br>            <SPAN style="color:#00007F">If</SPAN> rng.Value >= 1 <SPAN style="color:#00007F">Then</SPAN> rng.Offset(, 1).Value = <SPAN style="color:#00007F">Date</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[peta28]

the cell I want to input the date is cells (D4 and O52)

Thank you very much I'll try it and see if it works

 

[peta28]

hi,
it didnt work can anyone explain how to do this VBA the assigned cell for the dates are O52 and D4 please help thanks.

 

[Smitty]

What did you try?

 

[peta28]

I tried this code


Private Sub Worksheet_Change(ByVal Target As Range)
****'** Code goes in the Worksheet specific module
****Dim rng As Range
********'** Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
********Set rng = Target.Parent.Range("A1")
************ '** Only look at single cell changes
************If Target.Count > 1 Then Exit Sub
************'** Only look at that range
************If Intersect(Target, rng) Is Nothing Then Exit Sub
************'** Action if Condition(s) are met (do your thing here...)
************If Target.Value >= 1 Then Target.Offset(, 1).Value = Date
End Sub

Private Sub Worksheet_Calculate()
****Dim rng As Range
****
********Set rng = Range("A2")
********
************If rng.Value >= 1 Then rng.Offset(, 1).Value = Date
************
End Sub

It gave me 00/01/1900

 

[Smitty]

Both work for me, what cell format do you have applied to those cells?

 

[peta28]

o52 and d4

it's not that it did not show up in the cell it's that that dates were incorrect 00/01/1900 was what came up. Oh and these cell are in date format.

 

[Smitty]

Sounds like a formatting issue to me, so try:

.Offset(, 1).Value = Format(Date, "MM/DD/YY")