Entering a date that then gets changed to next weekday Thursday's date, say?

I don't want to make the current sheet I'm working on too complicated, so
prefer not to go the macro route.  Was hoping a formula would take care of
this (?).

If I enter a date via "^;", it would be so nice if the sheet knew to change
the date to the following Thursday's date.  i.e., when I type ^; into the
currently empty date cell, it puts today's date of "2006.12.27.Wed" which I
then stop to fix to nearest Thursday.  Instead, it would be very helpful if
a formula or something non-macro did that for me and changed it to the
nearest Thursday's date, in this case, "2006.12.28.Thu".

Can this be done?  I've spent considerable time in the archives but haven't
found anything that would help.  TIA.  :oD


0
NoSpam5247 (64)
12/27/2006 3:52:58 PM
excel 39879 articles. 2 followers. Follow

13 Replies
498 Views

Similar Articles

[PageSpeed] 35

Not without a macro.

-- 
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
news:eOELW8cKHHA.960@TK2MSFTNGP04.phx.gbl...
>I don't want to make the current sheet I'm working on too complicated, so
> prefer not to go the macro route.  Was hoping a formula would take care of
> this (?).
>
> If I enter a date via "^;", it would be so nice if the sheet knew to 
> change
> the date to the following Thursday's date.  i.e., when I type ^; into the
> currently empty date cell, it puts today's date of "2006.12.27.Wed" which 
> I
> then stop to fix to nearest Thursday.  Instead, it would be very helpful 
> if
> a formula or something non-macro did that for me and changed it to the
> nearest Thursday's date, in this case, "2006.12.28.Thu".
>
> Can this be done?  I've spent considerable time in the archives but 
> haven't
> found anything that would help.  TIA.  :oD
>
> 


0
bob.NGs (282)
12/27/2006 3:59:09 PM
"Bob Phillips" <bob.NGs@xxxx.com> wrote in message
news:ezNh3$cKHHA.1280@TK2MSFTNGP04.phx.gbl...
> Not without a macro.

Hmm, I wonder.  Excel is so fantastic that things that I thought would need
a macro, actually didn't and people came up with creative workarounds.  Of
course, I never figure out these things myself but it might be neat to see
if there are other opinions re this. <g>

Thanks.

> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
>
> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message
> news:eOELW8cKHHA.960@TK2MSFTNGP04.phx.gbl...
> >I don't want to make the current sheet I'm working on too complicated, so
> > prefer not to go the macro route.  Was hoping a formula would take care
of
> > this (?).
> >
> > If I enter a date via "^;", it would be so nice if the sheet knew to
> > change
> > the date to the following Thursday's date.  i.e., when I type ^; into
the
> > currently empty date cell, it puts today's date of "2006.12.27.Wed"
which
> > I
> > then stop to fix to nearest Thursday.  Instead, it would be very helpful
> > if
> > a formula or something non-macro did that for me and changed it to the
> > nearest Thursday's date, in this case, "2006.12.28.Thu".
> >
> > Can this be done?  I've spent considerable time in the archives but
> > haven't
> > found anything that would help.  TIA.  :oD
> >
> >
>
>


0
NoSpam5247 (64)
12/27/2006 4:48:45 PM
Overwriting the cell could not be done without a macro, as Bob has pointed 
out, but you could accomplish this without a macro if you use a helper 
column.

For example, if you want to put today's date into cell A1, then in whatever 
column you like you could put
=IF(WEEKDAY(A2)=5,A2,IF(WEEKDAY(A2)<5,A2+5-MOD(WEEKDAY(A2),5),A2+7-MOD(WEEKDAY(A2),5)))
to get the nearest Thursday (either that same day or the next one in the 
future).

If you always need the next Thursday (like you enter 12/28/06 but want it to 
return 1/4/07, then just take out the first IF statement and the last 
parenthesis.
=IF(WEEKDAY(A2)<5,A2+5-MOD(WEEKDAY(A2),5),A2+7-MOD(WEEKDAY(A2),5))

In this case, you'd probably label columnA with OriginalDate, DateEntered, 
or something similar that makes sense to you.  You can even put columnA 
outside your print area so it doesn't actually show on reports.  You can 
also make column A 0.5 points wide so that you hardly even know it's there, 
but you personally just happen to know to enter a date there that no one 
else needs to be concerned with.

Incidentally, you can just press ctrl+semicolon to input today's date.  Not 
sure what the carrot substitution thing is about, but thought I'd mention 
this built-in Excel keyboard shortcut.

- KC

"Bob Phillips" <bob.NGs@xxxx.com> wrote in message 
news:ezNh3$cKHHA.1280@TK2MSFTNGP04.phx.gbl...
> Not without a macro.
>
> -- 
> ---
> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
>
> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
> news:eOELW8cKHHA.960@TK2MSFTNGP04.phx.gbl...
>>I don't want to make the current sheet I'm working on too complicated, so
>> prefer not to go the macro route.  Was hoping a formula would take care 
>> of
>> this (?).
>>
>> If I enter a date via "^;", it would be so nice if the sheet knew to 
>> change
>> the date to the following Thursday's date.  i.e., when I type ^; into the
>> currently empty date cell, it puts today's date of "2006.12.27.Wed" which 
>> I
>> then stop to fix to nearest Thursday.  Instead, it would be very helpful 
>> if
>> a formula or something non-macro did that for me and changed it to the
>> nearest Thursday's date, in this case, "2006.12.28.Thu".
>>
>> Can this be done?  I've spent considerable time in the archives but 
>> haven't
>> found anything that would help.  TIA.  :oD
>>
>>
>
> 


0
12/27/2006 5:04:03 PM
Sorry, that should have said if you put today's date into cell A2.  I assume 
you have header rows. :)

"KC Rippstein" <kcrippstein@hotmail.com> wrote in message 
news:uMb1DkdKHHA.4928@TK2MSFTNGP06.phx.gbl...
> Overwriting the cell could not be done without a macro, as Bob has pointed 
> out, but you could accomplish this without a macro if you use a helper 
> column.
>
> For example, if you want to put today's date into cell A1, then in 
> whatever column you like you could put
> =IF(WEEKDAY(A2)=5,A2,IF(WEEKDAY(A2)<5,A2+5-MOD(WEEKDAY(A2),5),A2+7-MOD(WEEKDAY(A2),5)))
> to get the nearest Thursday (either that same day or the next one in the 
> future).
>
> If you always need the next Thursday (like you enter 12/28/06 but want it 
> to return 1/4/07, then just take out the first IF statement and the last 
> parenthesis.
> =IF(WEEKDAY(A2)<5,A2+5-MOD(WEEKDAY(A2),5),A2+7-MOD(WEEKDAY(A2),5))
>
> In this case, you'd probably label columnA with OriginalDate, DateEntered, 
> or something similar that makes sense to you.  You can even put columnA 
> outside your print area so it doesn't actually show on reports.  You can 
> also make column A 0.5 points wide so that you hardly even know it's 
> there, but you personally just happen to know to enter a date there that 
> no one else needs to be concerned with.
>
> Incidentally, you can just press ctrl+semicolon to input today's date. 
> Not sure what the carrot substitution thing is about, but thought I'd 
> mention this built-in Excel keyboard shortcut.
>
> - KC
>
> "Bob Phillips" <bob.NGs@xxxx.com> wrote in message 
> news:ezNh3$cKHHA.1280@TK2MSFTNGP04.phx.gbl...
>> Not without a macro.
>>
>> -- 
>> ---
>> HTH
>>
>> Bob
>>
>> (change the xxxx to gmail if mailing direct)
>>
>>
>> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
>> news:eOELW8cKHHA.960@TK2MSFTNGP04.phx.gbl...
>>>I don't want to make the current sheet I'm working on too complicated, so
>>> prefer not to go the macro route.  Was hoping a formula would take care 
>>> of
>>> this (?).
>>>
>>> If I enter a date via "^;", it would be so nice if the sheet knew to 
>>> change
>>> the date to the following Thursday's date.  i.e., when I type ^; into 
>>> the
>>> currently empty date cell, it puts today's date of "2006.12.27.Wed" 
>>> which I
>>> then stop to fix to nearest Thursday.  Instead, it would be very helpful 
>>> if
>>> a formula or something non-macro did that for me and changed it to the
>>> nearest Thursday's date, in this case, "2006.12.28.Thu".
>>>
>>> Can this be done?  I've spent considerable time in the archives but 
>>> haven't
>>> found anything that would help.  TIA.  :oD
>>>
>>>
>>
>>
>
> 


0
12/27/2006 5:12:54 PM
Hi

You could use Insert>Name>Define>Name  NxtThur
Refers to 
=INT(TODAY()/7)*7+5+7*(OR(WEEKDAY(TODAY())=5,WEEKDAY(TODAY())=6))

Then with Tools>Options>Spelling>Autocorrect Options you could choose a 
character you don't normally use
e.g. the � character found as the shifted leftmost top row of the UK 
keyboard
and have the substitution as =NxtThur

Typing � in a cell and pressing return would produce 04/01/2007 if 
entered today.
You would need to use a custom format of yyyy.mmm.dd.dddd to produce 
your requirement of
2007.Jan.04.Thursday



-- 
Regards

Roger Govier


"StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
news:O7F3gbdKHHA.5000@TK2MSFTNGP03.phx.gbl...
> "Bob Phillips" <bob.NGs@xxxx.com> wrote in message
> news:ezNh3$cKHHA.1280@TK2MSFTNGP04.phx.gbl...
>> Not without a macro.
>
> Hmm, I wonder.  Excel is so fantastic that things that I thought would 
> need
> a macro, actually didn't and people came up with creative workarounds. 
> Of
> course, I never figure out these things myself but it might be neat to 
> see
> if there are other opinions re this. <g>
>
> Thanks.
>
>> HTH
>>
>> Bob
>>
>> (change the xxxx to gmail if mailing direct)
>>
>>
>> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message
>> news:eOELW8cKHHA.960@TK2MSFTNGP04.phx.gbl...
>> >I don't want to make the current sheet I'm working on too 
>> >complicated, so
>> > prefer not to go the macro route.  Was hoping a formula would take 
>> > care
> of
>> > this (?).
>> >
>> > If I enter a date via "^;", it would be so nice if the sheet knew 
>> > to
>> > change
>> > the date to the following Thursday's date.  i.e., when I type ^; 
>> > into
> the
>> > currently empty date cell, it puts today's date of "2006.12.27.Wed"
> which
>> > I
>> > then stop to fix to nearest Thursday.  Instead, it would be very 
>> > helpful
>> > if
>> > a formula or something non-macro did that for me and changed it to 
>> > the
>> > nearest Thursday's date, in this case, "2006.12.28.Thu".
>> >
>> > Can this be done?  I've spent considerable time in the archives but
>> > haven't
>> > found anything that would help.  TIA.  :oD
>> >
>> >
>>
>>
>
> 


0
roger5293 (1125)
12/28/2006 9:43:22 AM
But that will change next week, unlike the Ctrl-; which inserts a static 
date. If he wants a static date, and no helper cell, I don't think it can be 
done without VBA.

-- 
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:OCTOgSmKHHA.4712@TK2MSFTNGP04.phx.gbl...
> Hi
>
> You could use Insert>Name>Define>Name  NxtThur
> Refers to 
> =INT(TODAY()/7)*7+5+7*(OR(WEEKDAY(TODAY())=5,WEEKDAY(TODAY())=6))
>
> Then with Tools>Options>Spelling>Autocorrect Options you could choose a 
> character you don't normally use
> e.g. the � character found as the shifted leftmost top row of the UK 
> keyboard
> and have the substitution as =NxtThur
>
> Typing � in a cell and pressing return would produce 04/01/2007 if entered 
> today.
> You would need to use a custom format of yyyy.mmm.dd.dddd to produce your 
> requirement of
> 2007.Jan.04.Thursday
>
>
>
> -- 
> Regards
>
> Roger Govier
>
>
> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
> news:O7F3gbdKHHA.5000@TK2MSFTNGP03.phx.gbl...
>> "Bob Phillips" <bob.NGs@xxxx.com> wrote in message
>> news:ezNh3$cKHHA.1280@TK2MSFTNGP04.phx.gbl...
>>> Not without a macro.
>>
>> Hmm, I wonder.  Excel is so fantastic that things that I thought would 
>> need
>> a macro, actually didn't and people came up with creative workarounds. Of
>> course, I never figure out these things myself but it might be neat to 
>> see
>> if there are other opinions re this. <g>
>>
>> Thanks.
>>
>>> HTH
>>>
>>> Bob
>>>
>>> (change the xxxx to gmail if mailing direct)
>>>
>>>
>>> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message
>>> news:eOELW8cKHHA.960@TK2MSFTNGP04.phx.gbl...
>>> >I don't want to make the current sheet I'm working on too complicated, 
>>> >so
>>> > prefer not to go the macro route.  Was hoping a formula would take 
>>> > care
>> of
>>> > this (?).
>>> >
>>> > If I enter a date via "^;", it would be so nice if the sheet knew to
>>> > change
>>> > the date to the following Thursday's date.  i.e., when I type ^; into
>> the
>>> > currently empty date cell, it puts today's date of "2006.12.27.Wed"
>> which
>>> > I
>>> > then stop to fix to nearest Thursday.  Instead, it would be very 
>>> > helpful
>>> > if
>>> > a formula or something non-macro did that for me and changed it to the
>>> > nearest Thursday's date, in this case, "2006.12.28.Thu".
>>> >
>>> > Can this be done?  I've spent considerable time in the archives but
>>> > haven't
>>> > found anything that would help.  TIA.  :oD
>>> >
>>> >
>>>
>>>
>>
>>
>
> 


0
bob.NGs (282)
12/28/2006 10:10:21 AM
Hi Bob

You are quite right, that it will not remain static like using Ctrl + ;
I must confess that seeing the OP's use of ^; made me think he was using 
the caret as a substitute character, and lead me off down my track. It 
is only on re-reading I can see the static nature.

Of course, if he wants it to be static, then without VBA he won't be 
able to achieve it, other than a manual Copy / Paste Special>Values.

May I wish you a Happy New Year.

-- 
Regards

Roger Govier


"Bob Phillips" <bob.NGs@xxxx.com> wrote in message 
news:OrHYohmKHHA.3936@TK2MSFTNGP02.phx.gbl...
> But that will change next week, unlike the Ctrl-; which inserts a 
> static date. If he wants a static date, and no helper cell, I don't 
> think it can be done without VBA.
>
> -- 
> ---
> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
>
> "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
> news:OCTOgSmKHHA.4712@TK2MSFTNGP04.phx.gbl...
>> Hi
>>
>> You could use Insert>Name>Define>Name  NxtThur
>> Refers to 
>> =INT(TODAY()/7)*7+5+7*(OR(WEEKDAY(TODAY())=5,WEEKDAY(TODAY())=6))
>>
>> Then with Tools>Options>Spelling>Autocorrect Options you could choose 
>> a character you don't normally use
>> e.g. the � character found as the shifted leftmost top row of the UK 
>> keyboard
>> and have the substitution as =NxtThur
>>
>> Typing � in a cell and pressing return would produce 04/01/2007 if 
>> entered today.
>> You would need to use a custom format of yyyy.mmm.dd.dddd to produce 
>> your requirement of
>> 2007.Jan.04.Thursday
>>
>>
>>
>> -- 
>> Regards
>>
>> Roger Govier
>>
>>
>> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
>> news:O7F3gbdKHHA.5000@TK2MSFTNGP03.phx.gbl...
>>> "Bob Phillips" <bob.NGs@xxxx.com> wrote in message
>>> news:ezNh3$cKHHA.1280@TK2MSFTNGP04.phx.gbl...
>>>> Not without a macro.
>>>
>>> Hmm, I wonder.  Excel is so fantastic that things that I thought 
>>> would need
>>> a macro, actually didn't and people came up with creative 
>>> workarounds. Of
>>> course, I never figure out these things myself but it might be neat 
>>> to see
>>> if there are other opinions re this. <g>
>>>
>>> Thanks.
>>>
>>>> HTH
>>>>
>>>> Bob
>>>>
>>>> (change the xxxx to gmail if mailing direct)
>>>>
>>>>
>>>> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message
>>>> news:eOELW8cKHHA.960@TK2MSFTNGP04.phx.gbl...
>>>> >I don't want to make the current sheet I'm working on too 
>>>> >complicated, so
>>>> > prefer not to go the macro route.  Was hoping a formula would 
>>>> > take care
>>> of
>>>> > this (?).
>>>> >
>>>> > If I enter a date via "^;", it would be so nice if the sheet knew 
>>>> > to
>>>> > change
>>>> > the date to the following Thursday's date.  i.e., when I type ^; 
>>>> > into
>>> the
>>>> > currently empty date cell, it puts today's date of 
>>>> > "2006.12.27.Wed"
>>> which
>>>> > I
>>>> > then stop to fix to nearest Thursday.  Instead, it would be very 
>>>> > helpful
>>>> > if
>>>> > a formula or something non-macro did that for me and changed it 
>>>> > to the
>>>> > nearest Thursday's date, in this case, "2006.12.28.Thu".
>>>> >
>>>> > Can this be done?  I've spent considerable time in the archives 
>>>> > but
>>>> > haven't
>>>> > found anything that would help.  TIA.  :oD
>>>> >
>>>> >
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
roger5293 (1125)
12/28/2006 10:48:19 AM
On Wed, 27 Dec 2006 10:52:58 -0500, StargateFanFromWork wrote in
microsoft.public.excel:

>I don't want to make the current sheet I'm working on too complicated, so
>prefer not to go the macro route.  Was hoping a formula would take care of
>this (?).
>
>If I enter a date via "^;", it would be so nice if the sheet knew to change
>the date to the following Thursday's date.  i.e., when I type ^; into the
>currently empty date cell, it puts today's date of "2006.12.27.Wed" which I
>then stop to fix to nearest Thursday.  Instead, it would be very helpful if
>a formula or something non-macro did that for me and changed it to the
>nearest Thursday's date, in this case, "2006.12.28.Thu".
>
>Can this be done?  I've spent considerable time in the archives but haven't
>found anything that would help.  TIA.  :oD

As others have already pointed out: not without a macro if you want the
result in the same cell.

As for formulas: put this in A2 and anter a date in A1:
  =A1-WEEKDAY(A1+4,2)+1+7
This will return next Thursday's date, including when you enter a
Thursday's date. If you want to return this Thursday's date when you
enter this Thursday, use this:
  =A1-WEEKDAY(A1+3,2)+7

-- 
Michael Bednarek   http://mbednarek.com/   "POST NO BILLS"
0
ROT13-zo (57)
12/28/2006 11:39:36 AM
And you my friend, one day I may make it up into darkest Wales <G>

Bob


"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:%23J3Jz2mKHHA.320@TK2MSFTNGP06.phx.gbl...
> Hi Bob
>
> You are quite right, that it will not remain static like using Ctrl + ;
> I must confess that seeing the OP's use of ^; made me think he was using 
> the caret as a substitute character, and lead me off down my track. It is 
> only on re-reading I can see the static nature.
>
> Of course, if he wants it to be static, then without VBA he won't be able 
> to achieve it, other than a manual Copy / Paste Special>Values.
>
> May I wish you a Happy New Year.
>
> -- 
> Regards
>
> Roger Govier
>
>
> "Bob Phillips" <bob.NGs@xxxx.com> wrote in message 
> news:OrHYohmKHHA.3936@TK2MSFTNGP02.phx.gbl...
>> But that will change next week, unlike the Ctrl-; which inserts a static 
>> date. If he wants a static date, and no helper cell, I don't think it can 
>> be done without VBA.
>>
>> -- 
>> ---
>> HTH
>>
>> Bob
>>
>> (change the xxxx to gmail if mailing direct)
>>
>>
>> "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
>> news:OCTOgSmKHHA.4712@TK2MSFTNGP04.phx.gbl...
>>> Hi
>>>
>>> You could use Insert>Name>Define>Name  NxtThur
>>> Refers to 
>>> =INT(TODAY()/7)*7+5+7*(OR(WEEKDAY(TODAY())=5,WEEKDAY(TODAY())=6))
>>>
>>> Then with Tools>Options>Spelling>Autocorrect Options you could choose a 
>>> character you don't normally use
>>> e.g. the � character found as the shifted leftmost top row of the UK 
>>> keyboard
>>> and have the substitution as =NxtThur
>>>
>>> Typing � in a cell and pressing return would produce 04/01/2007 if 
>>> entered today.
>>> You would need to use a custom format of yyyy.mmm.dd.dddd to produce 
>>> your requirement of
>>> 2007.Jan.04.Thursday
>>>
>>>
>>>
>>> -- 
>>> Regards
>>>
>>> Roger Govier
>>>
>>>
>>> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
>>> news:O7F3gbdKHHA.5000@TK2MSFTNGP03.phx.gbl...
>>>> "Bob Phillips" <bob.NGs@xxxx.com> wrote in message
>>>> news:ezNh3$cKHHA.1280@TK2MSFTNGP04.phx.gbl...
>>>>> Not without a macro.
>>>>
>>>> Hmm, I wonder.  Excel is so fantastic that things that I thought would 
>>>> need
>>>> a macro, actually didn't and people came up with creative workarounds. 
>>>> Of
>>>> course, I never figure out these things myself but it might be neat to 
>>>> see
>>>> if there are other opinions re this. <g>
>>>>
>>>> Thanks.
>>>>
>>>>> HTH
>>>>>
>>>>> Bob
>>>>>
>>>>> (change the xxxx to gmail if mailing direct)
>>>>>
>>>>>
>>>>> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message
>>>>> news:eOELW8cKHHA.960@TK2MSFTNGP04.phx.gbl...
>>>>> >I don't want to make the current sheet I'm working on too 
>>>>> >complicated, so
>>>>> > prefer not to go the macro route.  Was hoping a formula would take 
>>>>> > care
>>>> of
>>>>> > this (?).
>>>>> >
>>>>> > If I enter a date via "^;", it would be so nice if the sheet knew to
>>>>> > change
>>>>> > the date to the following Thursday's date.  i.e., when I type ^; 
>>>>> > into
>>>> the
>>>>> > currently empty date cell, it puts today's date of "2006.12.27.Wed"
>>>> which
>>>>> > I
>>>>> > then stop to fix to nearest Thursday.  Instead, it would be very 
>>>>> > helpful
>>>>> > if
>>>>> > a formula or something non-macro did that for me and changed it to 
>>>>> > the
>>>>> > nearest Thursday's date, in this case, "2006.12.28.Thu".
>>>>> >
>>>>> > Can this be done?  I've spent considerable time in the archives but
>>>>> > haven't
>>>>> > found anything that would help.  TIA.  :oD
>>>>> >
>>>>> >
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
bob.NGs (282)
12/28/2006 12:27:28 PM
On Thu, 28 Dec 2006 21:39:36 +1000, Michael Bednarek
<ROT13-zo@zorqanerx.pbz> wrote:

>On Wed, 27 Dec 2006 10:52:58 -0500, StargateFanFromWork wrote in
>microsoft.public.excel:
>
>>I don't want to make the current sheet I'm working on too complicated, so
>>prefer not to go the macro route.  Was hoping a formula would take care of
>>this (?).
>>
>>If I enter a date via "^;", it would be so nice if the sheet knew to change
>>the date to the following Thursday's date.  i.e., when I type ^; into the
>>currently empty date cell, it puts today's date of "2006.12.27.Wed" which I
>>then stop to fix to nearest Thursday.  Instead, it would be very helpful if
>>a formula or something non-macro did that for me and changed it to the
>>nearest Thursday's date, in this case, "2006.12.28.Thu".
>>
>>Can this be done?  I've spent considerable time in the archives but haven't
>>found anything that would help.  TIA.  :oD
>
>As others have already pointed out: not without a macro if you want the
>result in the same cell.
>
>As for formulas: put this in A2 and anter a date in A1:
>  =A1-WEEKDAY(A1+4,2)+1+7
>This will return next Thursday's date, including when you enter a
>Thursday's date. If you want to return this Thursday's date when you
>enter this Thursday, use this:
>  =A1-WEEKDAY(A1+3,2)+7

Thanks for everyone's responses.  I'll try out everything suggested
anyway.  That's how I learn.  The only problem is that I currently
can't save this workbook at all (I posted a new thread about this
particular problem).  Thanks!  :oD

0
StargateFan
12/28/2006 5:17:20 PM
hope this helps... i'm not sure if "following" means this thursday or next 
thursday. so i assume that any days from sunday to saturday this week 
entered will results in next week's thursday.  and also, this is not static 
but a formula as requested, so another column would be required to do this. 
oh, it should also work via autofill.  goodluck, orbii

lets say....
column A = today's date
column B = mod(today's date, 7)
column C = days till the "following" thusday

this formula should do the trick *note: replace B2 with mod(A2,7)
=IF(B2=0, A2+5, IF(B2=1, A2+4, IF(B2=2, A2+10,IF(B2=3, A2+9,IF(B2=4, A2+8, 
IF(B2=5, A2+7, IF(B2=6, A2+6, "ERROR")))))))

Wed 12.20.06    4     8     Thu 12.28.06
Thu 12.21.06    5     7     Thu 12.28.06
Fri 12.22.06     6     6     Thu 12.28.06
Sat 12.23.06     0     5     Thu 12.28.06
Sun 12.24.06     1     4     Thu 12.28.06
Mon 12.25.06     2     10     Thu 01.04.07
Tue 12.26.06     3     9     Thu 01.04.07
Wed 12.27.06     4     8     Thu 01.04.07


"StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
news:eOELW8cKHHA.960@TK2MSFTNGP04.phx.gbl...
>I don't want to make the current sheet I'm working on too complicated, so
> prefer not to go the macro route.  Was hoping a formula would take care of
> this (?).
>
> If I enter a date via "^;", it would be so nice if the sheet knew to 
> change
> the date to the following Thursday's date.  i.e., when I type ^; into the
> currently empty date cell, it puts today's date of "2006.12.27.Wed" which 
> I
> then stop to fix to nearest Thursday.  Instead, it would be very helpful 
> if
> a formula or something non-macro did that for me and changed it to the
> nearest Thursday's date, in this case, "2006.12.28.Thu".
>
> Can this be done?  I've spent considerable time in the archives but 
> haven't
> found anything that would help.  TIA.  :oD
>
> 


0
orbii (38)
12/29/2006 1:49:06 AM
dude you rock, i learned something really cool today thanks to you!!!
orbii

"Michael Bednarek" <ROT13-zo@zorqanerx.pbz> wrote in message 
news:4593ad31$0$4350$6c4959f3@news.easynews.nl...
> On Wed, 27 Dec 2006 10:52:58 -0500, StargateFanFromWork wrote in
> microsoft.public.excel:
>
>>I don't want to make the current sheet I'm working on too complicated, so
>>prefer not to go the macro route.  Was hoping a formula would take care of
>>this (?).
>>
>>If I enter a date via "^;", it would be so nice if the sheet knew to 
>>change
>>the date to the following Thursday's date.  i.e., when I type ^; into the
>>currently empty date cell, it puts today's date of "2006.12.27.Wed" which 
>>I
>>then stop to fix to nearest Thursday.  Instead, it would be very helpful 
>>if
>>a formula or something non-macro did that for me and changed it to the
>>nearest Thursday's date, in this case, "2006.12.28.Thu".
>>
>>Can this be done?  I've spent considerable time in the archives but 
>>haven't
>>found anything that would help.  TIA.  :oD
>
> As others have already pointed out: not without a macro if you want the
> result in the same cell.
>
> As for formulas: put this in A2 and anter a date in A1:
>  =A1-WEEKDAY(A1+4,2)+1+7
> This will return next Thursday's date, including when you enter a
> Thursday's date. If you want to return this Thursday's date when you
> enter this Thursday, use this:
>  =A1-WEEKDAY(A1+3,2)+7
>
> -- 
> Michael Bednarek   http://mbednarek.com/   "POST NO BILLS" 


0
orbii (38)
12/29/2006 2:09:42 AM
On Fri, 29 Dec 2006 09:49:06 +0800, "orbii" <orbii@hotmail.com> wrote:

>hope this helps... i'm not sure if "following" means this thursday or next 
>thursday. so i assume that any days from sunday to saturday this week 
>entered will results in next week's thursday.  and also, this is not static 
>but a formula as requested, so another column would be required to do this. 
>oh, it should also work via autofill.  goodluck, orbii
>
>lets say....
>column A = today's date
>column B = mod(today's date, 7)
>column C = days till the "following" thusday
>
>this formula should do the trick *note: replace B2 with mod(A2,7)
>=IF(B2=0, A2+5, IF(B2=1, A2+4, IF(B2=2, A2+10,IF(B2=3, A2+9,IF(B2=4, A2+8, 
>IF(B2=5, A2+7, IF(B2=6, A2+6, "ERROR")))))))
>
>Wed 12.20.06    4     8     Thu 12.28.06
>Thu 12.21.06    5     7     Thu 12.28.06
>Fri 12.22.06     6     6     Thu 12.28.06
>Sat 12.23.06     0     5     Thu 12.28.06
>Sun 12.24.06     1     4     Thu 12.28.06
>Mon 12.25.06     2     10     Thu 01.04.07
>Tue 12.26.06     3     9     Thu 01.04.07
>Wed 12.27.06     4     8     Thu 01.04.07

Kewl, thanks.  Will give this a shot.

>"StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
>news:eOELW8cKHHA.960@TK2MSFTNGP04.phx.gbl...
>>I don't want to make the current sheet I'm working on too complicated, so
>> prefer not to go the macro route.  Was hoping a formula would take care of
>> this (?).
>>
>> If I enter a date via "^;", it would be so nice if the sheet knew to 
>> change
>> the date to the following Thursday's date.  i.e., when I type ^; into the
>> currently empty date cell, it puts today's date of "2006.12.27.Wed" which 
>> I
>> then stop to fix to nearest Thursday.  Instead, it would be very helpful 
>> if
>> a formula or something non-macro did that for me and changed it to the
>> nearest Thursday's date, in this case, "2006.12.28.Thu".
>>
>> Can this be done?  I've spent considerable time in the archives but 
>> haven't
>> found anything that would help.  TIA.  :oD
>>
>> 
>

0
StargateFan
12/29/2006 3:33:50 AM
Reply:

Similar Artilces:

Microsoft Money 2003
I have been using Microsoft Money 2003 almost every day for years without problem until this weekend. Like others, I was locked out of my file and receive the message: “The sign-in name you entered is not associated with this file.” I have tried restoring from the backup, but this gave the same error. I have tried the procedure suggested at: Article ID: 902466 but this requires a password to repair the file and so is useless in this case. I have tried everything at: Article ID: 891338 without success. I see that back in 2004, PC World published an article about when all users ...

Mystery price changes-anybody experienced????
When I have been updating items in Manager, sometimes the price from the previous item automatically populates and changes on the next item. Has anybody experienced this? Very frustrating. Any suggestions? Apply the latest hotfix for RMS. This will resolve the issue. Mihir Diviasoft, Inc www.diviasoft.com "knightsbridge" <knightsbridge@discussions.microsoft.com> wrote in message news:9BF21959-BD09-4005-B635-409A2EBD4570@microsoft.com... > When I have been updating items in Manager, sometimes the price from the > previous item automatically populates and changes o...

duplex printing question, change record order to line up with other side?
i have a report that i need to make the opposite side match up with the correct record. it is setup to print 4 records, 2 across then 2 below. like 4 post cards on a landscape page. records for each customer need to match up with the mailing address on the reverse side. so the records are switched if i were to make the report right now. basicly records start 1 and 2 on the top of the landscaped page then 3 and 4 under. so on the reverse side records need to be 2 and 1 then 4 and 3 under. i havent made the opposite side for mailing address yet. i need some info on how i need to do this....

How do I get Excel to open a previously saved file?
With Excel not opened, all of sudden when I try to open an existing Excel file, it only opens a blank workbook. I then have to repeat the action of opening the file from the blank workbook. I am not sure what has happened but I have always just been able to just click on the Excel file I want to open and it would automatically open Excel and the intended file. Please advise hi, check this....... Tools>options>general Tab... Make sure that Ignore other applications is unchecked. that's all i can think of Regards FSt1 "Jeff S" wrote: > With Excel not opened, all of ...

Change date format on report
My date displays as mddyy in the table but on the report I'd like it to display as mm/dd/yyyy, how would I code that to have that field always display in mm/dd/yyyy format. I'm thinking this is very simple but I just can't get it to work for me. Thank so much for your help. On May 2, 1:50 pm, SITCFanTN <SITCFa...@discussions.microsoft.com> wrote: > My date displays as mddyy in the table but on the report I'd like it to > display as mm/dd/yyyy, how would I code that to have that field always > display in mm/dd/yyyy format. I'm thinking this is very simple...

Why does spell check change to English(US) on replies/forwards?
My default language is set to English(UK) spell checker reverts to English (US) when replying or forwarding messages. How can I change this so it is always English(UK)?Cheers,Tom ...

Combo Box changes after Microsoft Upgrade.
I have WindowsXP and Access2003. I have a Combo Box on a form used for finding a particular record. After downloading and installing the Office2003 upgrade (Office 2003 Service Pack 3 (SP3)), one of the columns (a text) Combo Box is blank. This remains blank even if I create a new Combo Box. Also, going back to a application backup I made in January2007, this column has become blank too. The Office 2003 Service Pack 3 (SP3) is the only event which has occurred. Please help, Frank And, you've hit the nail on the head. It is SP3 which is causing this issue. From what I u...

Entering Numbers with 2 Decimal Places
Hi, I've been using Excel for years, yet this is my first time on this newsgroup. When reconciling my checkbook, I can set my 10-key calculator to 2 decimal places, so that whenever I enter a number, it automatically converts it to dollars and cents. Is there a way to do this in Excel so that I don't have to hit the "." ? Thanks, Bruce Choose Tools>Options On the Edit tab, add a check mark to Fixed Decimals Set the number of places to 2 Click OK Bruce wrote: > Hi, > > I've been using Excel for years, yet this is my first time on this > newsgroup....

Sumproduct
I am using Sumproduct with three variables (Person, Product and Month). I am using data validation lists for the end user to select the person, product and month. I would like to be able to add a "Total" to the person list so that when the end user chooses "Total" and a product and month that the Sumproduct would only use the two other criteria (Product and Month) and be able to see the totals of those criteria for all Persons. I know I could nest IF THEN statements with the Sumproduct, but is there a better way??? Appreciate the help! No, an IF/THEN m...

Get a specifc button Handle inside ribbon
Hi, Im trying to make a program that clicks on a specific button inside a specif ribbon. I think i got use sendMessage to complete this task but first i need to find the handle of the button. So far i can get the handle oh the excel and then i used EnumChild Windows to find childs handles. But looking at the child texts i cant get anything thats looks with a the ribbon or the button. Can some one helpme? Should i look inside an specif child to find the ribbon and then inside the ribbon to find the button? if yes which one? Thnaks Sink I don't think the ribbon exposes ...

Max Date + condition
Hi all! In column 1, I have dates. In column 2, I have criterias. How can I ge the max date matching criteria in column 2. Example: Col 1 2004-08-01 2004-08-01 2004-08-02 2004-08-01 2004-08-05 Col 2 AA ABC AA CC CC How to get max date of AA ? I should get 2004-08-02 Thank you -- Message posted from http://www.ExcelForum.com Hi try the array formula (entered with CTRL+sHIFT+ENTER): =MAX(IF(B1:B100="AA",A1:A100)) >-----Original Message----- >Hi all! > >In column 1, I have dates. In column 2, I have criterias. How can I get >the max date matching criteria in co...

Can't Use Outlook after changing Servers
Moved users from one server to another. Users map USER folder to U:\ drive, no chnage there. After move when trying to open Outlook it stops with an error that there is a security problem with the .pst file. There is none I can find. Tried deleting Outlook profile for user and installing a new one, came up with same pst file. tried adding a second pst file and get the same security violation, No other files have problems, WORD or Excel. ideas?? dave Admin ...

How to change the year?
I have a date column in my work sheet where I have enterred the year as 2005, but actually should be 2001. The cell format is date-month-year. My date range is B5:B55. How can I change the year on only for the dates in this range. Thanks. Victor Victor, The difference between a 2001 and a 2005 date is 1461. So do this: Put 1461 in a cell. Copy. Select B5:B55. Edit - Paste Special. Click "Values" and "Subtract." OK. -- Earl Kiosterud www.smokeylake.com "la90292" <la90292@myrealbox.com> wrote in message news:ebnzG62qFHA.3720@TK2MSFTNGP14.phx.gb...

How do I get email in Outlook Express on an XP machine into Outlook 2007 on Windows 7 Machine?
The instructions for importing Outlook Express files from another machine assume that Outlook Express can be run on the target machine. I don't believe there is a way to do that on a Windows 2007 machine. So How do I accomplish this? On Fri, 20 Nov 2009 18:03:05 -0800, MikeH <mikeh@somewhere.net> wrote: > The instructions for importing Outlook Express files from another > machine assume that Outlook Express can be run on the target machine. I > don't believe there is a way to do that on a Windows 2007 machine. So > How do I accomplish this? F...

change range for multiple charts
Hi I need to change the range for multiple charts, i.e. i have to go chart by chart and modify, either with the mouse or by typing, the new range, is there a quicker way to do this? "Yossi evenzur" wrote: > Hi > I need to change the range for multiple charts, i.e. i have to go chart by > chart and modify, either with the mouse or by typing, the new range, is there > a quicker way to do this? I've written a little Excel add-in that performs mass edits of series formulas: How to Edit Series Formulas http://peltiertech.com/WordPress/how-to-edit...

Important dates Gone
Hi, Went from 2003 to 2004. In 2003 I had money express and my home page show important dates. Via the options the dates are still there. But don't show up any more? Anyone knows how I can fix this? Thanks Jeff ...

Change PST?
I installed a new Outlook (XP) and have my old backup PST file on a disk. I want the default data location to be of my choosing. I assume I copy that data where I want it but how do I import it? "bobnewman backup.pst.pst". I am totally confused, both by the lack of documentation (that I can find) and the backup file name. I hope someone can help. Bob I found it! Thanks anyway. Bob "Bob Newman" <bobnewman@att.net> wrote in message news:vD%7d.2900$TY2.272@lakeread04... > I installed a new Outlook (XP) and have my old backup PST file on a disk. I > want t...

Date Header and Layout Questions
I have 1 table with headings of: TaskType SiteName StartDate EndDate On a report, I want the header to show every Monday's date going horizontally displaying every Monday from now until 2 years from now. Then I want the Vertical column to be the TaskType. Within the report I want the SiteName to be identified in both the Monday's Date Column and the TaskType Column. For example, 30-Nov-09 (Monday) TaskType-DSR SiteName - DC. The date being on the header, the TaskType on each row and the SiteName cross referenced between them. Does this make any sense? ...

How do I change the elements displayed in the message list
How do I change the elements displayed in the message list. For example, to list "To" or "From" edit the view - to change just one folder, click on the row of field names and choose custom. To make all folders the same see http://www.outlook-tips.net/howto/grouping.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips: http://www.outlook-tips...

get data form cells sequentially
Hi there, I have a spreadsheet i am creating which is giving me some amount o hassle!!! I'm sure it is possible to do this but i just cant figure ou how:- I have one worksheet which has many copies of the same table, eac table is consistantly ontop of the other i.e. first table uses cells A - G10 (a rectangle of cells). The dates i am trying to use are in a pattern of a nine row seperatio i.e. first date is in cell G2, next one is G11, next one G20, and s on. I need to find out how i can make a formula which i could copy whic would grab the date out of these cells in sequence for pl...

SBS 2008 Reboot
I have a client who needed to reboot their server this AM (we couldn't log onto RWW, when this happens, they reboot. It happens once every couple of months.) Upon rebooting, they called and are telling me they are getting a windows titled "Windows Complete PC Restore". Why would this come up on a normal reboot? Did they have a failure of some sort? Can we get by this screen to get the server back online? I thought I would try here first, as I cannot get to the site until later this afternoon. Thank you in advance. -- Ron Anthony Quinn Is the SBS2008 DVD ...

Two cells divided into next cell
Hope this makes sense. I have F column that totals down to F16. Then I have a total on D16. I need both totals to be divided and show up on G16. Every cell has $ Amts, but the only cells not totaled down the row are E,G and J. So G16 is blank. Is this possible to do and If so how. I don't even know the formula to Divide and I need this to automatically happen every time I Insert the Amts in the cells. Please help In G16: =D16/F16 "Day" <Day@discussions.microsoft.com> wrote in message news:62C49FCC-273A-425B-9EEF-DCF2109A0DD9@microsoft.com... > > ...

Money using wrong date to calculate ROI YTD
I have opened a new file in Money to track investments for a stock club. I entered all transactions, which go back to Jan. 2002. When I look at performance for the account, the ROI YTD is not accurate, as Money is using values for the stocks going back only 6 months form the date I enterd the transactions rather than the values as of 1/1/03. What to do? In microsoft.public.money, Brooks wrote: >I have opened a new file in Money to track investments for >a stock club. I entered all transactions, which go back >to Jan. 2002. When I look at performance for the account, &g...

Find the number of years between two dates
I have aroster of players with thier date of births. I would like to automatically add their ages in another column Try this Formula, assuming that the dob is in cell A1 =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" regards Mukesh "duke" wrote: > I have aroster of players with thier date of births. I would like to > automatically add their ages in another column On Fri, 9 Sep 2005 19:47:02 -0700, "duke" <duke@disc...

Word always asks to save changes to the template
For my work we have made templates for different letters we have to send. Our application (VB6) calls those templates when we want to send a letter. Using the code we follow a few steps : - open word - open a new document using our template - save the document (.doc) on a server - sometimes print the document - sometimes immediately close the document (when we don't want users to modify the document afterwards) When they closed the document the users always get 2 questions : - save changes to template for the letter - save changes to normal.dot I already found a solution to avoid the q...