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

[StargateFanFromWork]

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. D

 

[Bob Phillips]

Not without a macro.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[StargateFanFromWork]

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.

 

[KC Rippstein]

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

 

[KC Rippstein]

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

 

[Roger Govier]

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

 

[Bob Phillips]

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]

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.

 

[Michael Bednarek]

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. D

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

 

[Bob Phillips]

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

Bob

 

[StargateFan]

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! D

 

[orbii]

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

 

[orbii]

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

 

[StargateFan]

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.