auto working week-ending date for timesheet
 

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

"Dm76" wrote in message
...
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.

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?

"Biff" wrote:

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

"Dm76" wrote in message
...
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.

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

"Dm76" wrote in message
...
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?

"Biff" wrote:

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

"Dm76" wrote in message
...
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.

auto working week-ending date for timesheet
 

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


"Dm76" wrote:

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?

"Biff" wrote:

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

"Dm76" wrote in message
...
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.