Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
search for latest date | Excel Worksheet Functions | |||
Date, Day of week | Excel Worksheet Functions | |||
assign auto number and auto date | Excel Discussion (Misc queries) | |||
excel links update not working in auto, calculations in auto | Excel Worksheet Functions |