auto working week-ending date for timesheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to have an auto updating date for a timesheet. At the start of
the week the date would show the date at the end of the week. At the start of
each week the date would change to the end date of that week.
 
Hi!

It's not real clear what you want.

If you want a date that is always the Sunday of that week:

=TODAY()-WEEKDAY(TODAY(),2)+7

Biff
 
Thanks that worked well. Is it possible to have the function only update when
the file is saved and not at any other time. e.g. when opened or data changed
in the cell?
 
Instead of a formula you need an event macro. I can't help you with that.

Maybe someone will follow-up with a macro or you can post this in the
programming forum.

Biff
 
Here is what I do in VBA my date field is S2 (2,19) and my week ends on
Saturday

Private Sub Workbook_Open()
' Unprotect protected cells to add user name
Worksheets("Time Report").Unprotect (UserInterfaceOnly)
'Get User name
Worksheets("Time Report").Cells(2, 3) = Worksheets("Time
Report").Application.UserName
If 7 - Weekday(Date, vbSunday) + Date <> Sheet2.Cells(2, 19).Value Then
If MsgBox("Week Ending: " + Str(Worksheets("Time Report").Cells(2,
19).Value) + _
" Is not this week, Do you want to start a new week?", vbYesNo Or
vbQuestion, "Start a New Week?") = vbYes Then
Worksheets("Time Report").Cells(2, 19) = 7 - Weekday(Date,
vbSunday) + Date
bNew = True
End If
End If
If bNew Then
fname = "CSTS " & Str(Worksheets("Time Report").Cells(2, 19).Value2)
& " " & _
Worksheets("Time Report").Application.UserName
ThisWorkbook.SaveAs Filename:=fname
End If
bNew = False
' Protect worksheet again
Worksheets("Time Report").Protect (UserInterfaceOnly)
End Sub
 
Back
Top