Date functions

  • Thread starter Thread starter Kathy Dennis
  • Start date Start date
K

Kathy Dennis

For a timesheet, we need to show the 2 week period the
timesheet covers. I can use the TODAY() function for the
ending date, but could use some assistance in pulling in
the beginning date for that 2 week period.
 
Kathy Dennis said:
For a timesheet, we need to show the 2 week period the
timesheet covers. I can use the TODAY() function for the
ending date, but could use some assistance in pulling in
the beginning date for that 2 week period.
Click to expand...

Could you use a formula such as this?
=TODAY()-13
Or, if A1 contains =TODAY(),
=A1-13
These will give the date two weeks less a day before today, which is
probably when your period started.

Of course, TODAY() is a volatile function. Open the workbook tomorrow and it
will show tomorrow's date. If what you really wanted was to enter today's
date and have it stay the same ever thereafter, don't use TODAY() but
instead use CTRL+; (hold down CTRL whilst pressing the semicolon key).
 
If you want Sunday, June 29th to be the beginning date of the current 2 week
period:

=MOD(WEEKNUM(TODAY()),2)*7+TODAY()-WEEKDAY(TODAY())-6

On the other hand, if you want Sunday, July 6th to be the beginning date of
the current 2 week period:

=NOT(MOD(WEEKNUM(TODAY()),2))*7+TODAY()-WEEKDAY(TODAY())-6

These formulas will work on any date during that period.
 
Back
Top