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

peta28

New Member
Joined
Jul 30, 2010
Messages
5
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 :)
 
Last edited:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,451
Would this work...
Code:
=IF(SUM(H46, I46, O82, O113)>0,TODAY()-WEEKDAY(TODAY(),1)+1,"")
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Jul 30, 2010
Messages
5
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

New Member
Joined
Jul 30, 2010
Messages
5

ADVERTISEMENT

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.
 

peta28

New Member
Joined
Jul 30, 2010
Messages
5

ADVERTISEMENT

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

Legend
Joined
May 15, 2003
Messages
29,536
Both work for me, what cell format do you have applied to those cells?
 

peta28

New Member
Joined
Jul 30, 2010
Messages
5
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.
 
Last edited:

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Sounds like a formatting issue to me, so try:

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,108
Messages
5,857,428
Members
431,879
Latest member
KiwDaWabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top