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:

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
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")
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,613
Messages
5,512,422
Members
408,891
Latest member
youngbella

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top