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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StargateFanFromWork - 27 Dec 2006 15:52 GMT

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

Reply to this Message

Bob Phillips - 27 Dec 2006 15:59 GMT

Not without a macro.

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

>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
[quoted text clipped - 13 lines]
> haven't
> found anything that would help. TIA. :oD

Reply to this Message

StargateFanFromWork - 27 Dec 2006 16:48 GMT

> 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
>
[quoted text clipped - 19 lines]
> > haven't
> > found anything that would help. TIA. :oD

Reply to this Message

Roger Govier - 28 Dec 2006 09:43 GMT

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

Signature

Regards

Roger Govier

>> Not without a macro.
>
[quoted text clipped - 40 lines]
>> > haven't
>> > found anything that would help. TIA. :oD

Reply to this Message

Bob Phillips - 28 Dec 2006 10:10 GMT

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.

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Hi
>
[quoted text clipped - 54 lines]
>>> > haven't
>>> > found anything that would help. TIA. :oD

Reply to this Message

Roger Govier - 28 Dec 2006 10:48 GMT

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.

Signature

Regards

Roger Govier

> 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
[quoted text clipped - 64 lines]
>>>> > haven't
>>>> > found anything that would help. TIA. :oD

Reply to this Message

Bob Phillips - 28 Dec 2006 12:27 GMT

And you my friend, one day I may make it up into darkest Wales <G>

Bob

> Hi Bob
>
[quoted text clipped - 73 lines]
>>>>> > haven't
>>>>> > found anything that would help. TIA. :oD

Reply to this Message

KC Rippstein - 27 Dec 2006 17:04 GMT

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

> Not without a macro.
>
[quoted text clipped - 16 lines]
>> haven't
>> found anything that would help. TIA. :oD

Reply to this Message

KC Rippstein - 27 Dec 2006 17:12 GMT

Sorry, that should have said if you put today's date into cell A2. I assume
you have header rows. :)

> 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
[quoted text clipped - 45 lines]
>>> haven't
>>> found anything that would help. TIA. :oD

Reply to this Message

Michael Bednarek - 28 Dec 2006 11:39 GMT

>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
[quoted text clipped - 9 lines]
>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

Signature

Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

Reply to this Message

StargateFan - 28 Dec 2006 17:17 GMT

>>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
[quoted text clipped - 19 lines]
>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

Reply to this Message

orbii - 29 Dec 2006 02:09 GMT

dude you rock, i learned something really cool today thanks to you!!!
orbii

>>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
[quoted text clipped - 23 lines]
> enter this Thursday, use this:
> =A1-WEEKDAY(A1+3,2)+7

Reply to this Message

orbii - 29 Dec 2006 01:49 GMT

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

Reply to this Message

StargateFan - 29 Dec 2006 03:33 GMT

>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
[quoted text clipped - 19 lines]
>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.

>>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
[quoted text clipped - 13 lines]
>> haven't
>> found anything that would help. TIA. :oD

Reply to this Message

Rate this thread: