Calculate the closest day

[Jim]

Hello,

I am creating a time card. I would like a formula that will calculate
(show) Fridays date closest to the date when the spreadsheet is opened. For
example If I opened the sheet today the formula would populate yesterdays
date. If I opened the sheet on Tuesday, the sheet formula will populate next
Fridays date, etc…

Thanks

[CLR]

Assuming the date of the sheet opening is in A1.....

=TEXT(A1+LOOKUP(WEEKDAY(A1),{1,2,3,4,5,6,7},{-2,-3,3,2,1,0,-1}),"mm/dd/yyyy
dddd")

Vaya con Dios,
Chuck, CABGx3



"Jim" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I am creating a time card. I would like a formula that will calculate
> (show) Fridays date closest to the date when the spreadsheet is opened.
For
> example If I opened the sheet today the formula would populate yesterdays
> date. If I opened the sheet on Tuesday, the sheet formula will populate
next
> Fridays date, etc.
>
> Thanks
>

[daddylonglegs]

Try this formula

=TODAY()+4-WEEKDAY(TODAY()-2)

[Jim]

perfect

"CLR" wrote:

> Assuming the date of the sheet opening is in A1.....
>
> =TEXT(A1+LOOKUP(WEEKDAY(A1),{1,2,3,4,5,6,7},{-2,-3,3,2,1,0,-1}),"mm/dd/yyyy
> dddd")
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Jim" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I am creating a time card. I would like a formula that will calculate
> > (show) Fridays date closest to the date when the spreadsheet is opened.
> For
> > example If I opened the sheet today the formula would populate yesterdays
> > date. If I opened the sheet on Tuesday, the sheet formula will populate
> next
> > Fridays date, etc.
> >
> > Thanks
> >
>
>
>