Monday closest to today's date?
in [Functions]
|
From: Ted on 16 Mar 2010 09:50 Hello: I am seeking a formula that would return the date of the closest Monday to today's date. The monday would have to be the first one in the future, not the past. So today, Tuesday March 16, 2010, the formula would return Monday, March 22, 2010 Thanks in advance :)
From: zxcv on 16 Mar 2010 10:02 On Mar 16, 9:50 am, "Ted" <ted.gallag...(a)gmail.com> wrote: > Hello: > > I am seeking a formula that would return the date of the closest Monday to > today's date. The monday would have to be the first one in the future, not > the past. So today, Tuesday March 16, 2010, the formula would return > Monday, March 22, 2010 > > Thanks in advance :) How about this? =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+7-WEEKDAY(TODAY(),3))
From: Luke M on 16 Mar 2010 10:07 If today is Monday, do you want today's date or next week? former: =TODAY()+CHOOSE(WEEKDAY(TODAY()),1,0,6,5,4,3,2) latter: =TODAY()+CHOOSE(WEEKDAY(TODAY()),1,7,6,5,4,3,2) -- Best Regards, Luke M "Ted" <ted.gallagher(a)gmail.com> wrote in message news:OA4Ch%23QxKHA.5940(a)TK2MSFTNGP02.phx.gbl... > Hello: > > I am seeking a formula that would return the date of the closest Monday to > today's date. The monday would have to be the first one in the future, > not the past. So today, Tuesday March 16, 2010, the formula would return > Monday, March 22, 2010 > > Thanks in advance :)
From: Ted on 16 Mar 2010 10:19 Thank you; While I did not say so, I need to do the same for each day of the week. If I change the '3' in the formula to 1, it returns Saturday, March 20. Change it to 2 and it returns Sunday March 21. That is good. But if I change it to 4, in the hopes it will return Tuesday, March 23, I get an error. Same if I change it to 6 in the hopes it returns Thursday, March 18. What am I missing? Thanks again. "zxcv" <zxcvnosend(a)yahoo.com> wrote in message news:54af0957-9d05-435e-b9a9-abc7912ad4a5(a)u9g2000yqb.googlegroups.com... > On Mar 16, 9:50 am, "Ted" <ted.gallag...(a)gmail.com> wrote: >> Hello: >> >> I am seeking a formula that would return the date of the closest Monday >> to >> today's date. The monday would have to be the first one in the future, >> not >> the past. So today, Tuesday March 16, 2010, the formula would return >> Monday, March 22, 2010 >> >> Thanks in advance :) > > How about this? > > =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+7-WEEKDAY(TODAY(),3))
From: Luke M on 16 Mar 2010 10:35 You should look up how the WEEKDAY function works. The last arguement can not be changed arbitrarily.. If you're wanting a different day, something like: =TODAY()+7-WEEKDAY(TODAY(),3) If you're wanting a different day, decrease the 7. 6 - Sunday 3/21 5 - Sat 3/20 4 - Fri 3/19 -- Best Regards, Luke M "Ted" <ted.gallagher(a)gmail.com> wrote in message news:%23NJv4ORxKHA.1796(a)TK2MSFTNGP02.phx.gbl... > Thank you; > > While I did not say so, I need to do the same for each day of the week. > If I change the '3' in the formula to 1, it returns Saturday, March 20. > Change it to 2 and it returns Sunday March 21. That is good. But if I > change it to 4, in the hopes it will return Tuesday, March 23, I get an > error. Same if I change it to 6 in the hopes it returns Thursday, March > 18. > > > What am I missing? > > Thanks again. > > "zxcv" <zxcvnosend(a)yahoo.com> wrote in message > news:54af0957-9d05-435e-b9a9-abc7912ad4a5(a)u9g2000yqb.googlegroups.com... >> On Mar 16, 9:50 am, "Ted" <ted.gallag...(a)gmail.com> wrote: >>> Hello: >>> >>> I am seeking a formula that would return the date of the closest Monday >>> to >>> today's date. The monday would have to be the first one in the future, >>> not >>> the past. So today, Tuesday March 16, 2010, the formula would return >>> Monday, March 22, 2010 >>> >>> Thanks in advance :) >> >> How about this? >> >> =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+7-WEEKDAY(TODAY(),3)) >
|
Next
|
Last
Pages: 1 2 Prev: Yearly sum Next: delete columns in spreadsheet after data entered so it wont scroll |