On a bi-weekly time sheet, I need 2 cells populated with the "week-ending"
dates of the 2 past weeks. These dates are always the 2 previous Fridays,
but the spreadsheet may be accessed before, on or after the 2nd Friday
(within a couple days).

For example, today we're submitting time sheets for the weeks ending 12/22
and 12/29. Some folks may have filled theirs out yesterday, and some
stragglers will fill them out on Monday. I would like the spreadsheet to
pre-fill with those 2 week-ending dates, in 2 separate cells.

Possible?

Thanks,
Sherri

Hi!

12/29 is not a previous Friday. It is the Friday of the current week.

If you want the current week Friday and the previous week Friday:

A1 = Current week Friday:

=TODAY()-WEEKDAY(TODAY(),3)+4

A2 = Previous week Friday:

=A1-7

Biff

"Sherri" wrote in message
...
On a bi-weekly time sheet, I need 2 cells populated with the "week-ending"
dates of the 2 past weeks. These dates are always the 2 previous Fridays,
but the spreadsheet may be accessed before, on or after the 2nd Friday
(within a couple days).

For example, today we're submitting time sheets for the weeks ending 12/22
and 12/29. Some folks may have filled theirs out yesterday, and some
stragglers will fill them out on Monday. I would like the spreadsheet to
pre-fill with those 2 week-ending dates, in 2 separate cells.

Possible?

Thanks,
Sherri

The tricky part is that sometimes people don't fill out their timesheet until
the following Monday, so it wouldn't be the current week...?

"T. Valko" wrote:

Hi!

12/29 is not a previous Friday. It is the Friday of the current week.

If you want the current week Friday and the previous week Friday:

A1 = Current week Friday:

=TODAY()-WEEKDAY(TODAY(),3)+4

A2 = Previous week Friday:

=A1-7

Biff

"Sherri" wrote in message
...
On a bi-weekly time sheet, I need 2 cells populated with the "week-ending"
dates of the 2 past weeks. These dates are always the 2 previous Fridays,
but the spreadsheet may be accessed before, on or after the 2nd Friday
(within a couple days).

For example, today we're submitting time sheets for the weeks ending 12/22
and 12/29. Some folks may have filled theirs out yesterday, and some
stragglers will fill them out on Monday. I would like the spreadsheet to
pre-fill with those 2 week-ending dates, in 2 separate cells.

Possible?

Thanks,
Sherri

Sherri

To get the first Friday you could use:

=IF(WEEKDAY(TODAY())=6,TODAY(),IF(WEEKDAY(TODAY()) =7,TODAY()-1,TODAY()-WEEKDAY(TODAY())-1))

The previous Friday would be the first Friday minus 7.

You could use something like the nested IFs above to decide what Friday
you are interested in depending on what day of the week it is. If it
is Friday, it seems you want the first Friday today, but if today is
Monday would the first Friday of interest be the one 4 days later, or
the one 3 days earlier?

Good luck.

Ken
Norfolk, Va


Sherri wrote:
The tricky part is that sometimes people don't fill out their timesheet until
the following Monday, so it wouldn't be the current week...?

"T. Valko" wrote:

Hi!

12/29 is not a previous Friday. It is the Friday of the current week.

If you want the current week Friday and the previous week Friday:

A1 = Current week Friday:

=TODAY()-WEEKDAY(TODAY(),3)+4

A2 = Previous week Friday:

=A1-7

Biff

"Sherri" wrote in message
...
On a bi-weekly time sheet, I need 2 cells populated with the "week-ending"
dates of the 2 past weeks. These dates are always the 2 previous Fridays,
but the spreadsheet may be accessed before, on or after the 2nd Friday
(within a couple days).

For example, today we're submitting time sheets for the weeks ending 12/22
and 12/29. Some folks may have filled theirs out yesterday, and some
stragglers will fill them out on Monday. I would like the spreadsheet to
pre-fill with those 2 week-ending dates, in 2 separate cells.

Possible?

Thanks,
Sherri