Change to formula to make it go to next weekday (i.e., avoid weekend days)?

[StargateFan]

I have a very specific formula that I need to keep the same. However,
I need to make the output come out as a weekday at all times. How can
I do this pls?

=CHOOSE(WEEKDAY(TODAY()+1),"Sn","Mn","Tu","Wd","Th","Fr","Sa") &
TEXT(TODAY()+1,"\.mmm.dd.yyyy")

Thanks so much!! D

 

[Jim Cone]

You want to keep the formula unchanged, but get a different result. That's funny.
Here is what I might do...
=CHOOSE(WEEKDAY(TODAY()),TODAY()+1,TODAY()+1,TODAY()+1,TODAY()+1,TODAY()+1,TODAY()+3,TODAY()+2)
Format the cell as: ddd.mmmm.dd.yyyy
'---
Jim Cone
Portland, Oregon USA
http://blog.contextures.com/archives/2011/07/18/find-last-row-with-excel-vba/
(workbook with "universal" Last Row function code - free)





"StargateFan" <[email protected]>
wrote in message
news:20dca371-fa04-4756-8298-261ed4c28a1a@h15g2000yqa.googlegroups.com...

 

[StargateFan]

You want to keep the formula unchanged, but get a different result. That's funny.

Yeah, bad wording on my part ... <g> but I think you get the idea,
nonetheless. What I meant was the basics of the formula, of course.
I've found recently that if I don't say that I need to not take out
elements but just to add what is needed, that the suggested formulas
gets changed drastically from the original and the conditions aren't
then all met. I just didn't word it right ... typing messages in the
mornings while trying to hurry to work do that sometimes. Hope it

Here is what I might do...
=CHOOSE(WEEKDAY(TODAY()),TODAY()+1,TODAY()+1,TODAY()+1,TODAY()+1,TODAY()+1,TODAY()+3,TODAY()+2)
Format the cell as: ddd.mmmm.dd.yyyy

Thanks. Woops, drat. See? That's exactly what I mean ... <g> I'm
not handy with formulas, but I'll have to see how to add back in my
special date formatting <sigh>. Well, though it'll probably take me
tons more time than you guys. (That's what I meant by keeping things
the same that don't need changing but just to add the additional
condition to avoid weekend days P.)


Thanks. D

(Coming back to this in same message ...) Going to try the following
below, which was just simply copy/pasting in the bit above to the
conditions part of the formula. Simple copy/pasting from one formula
to another doesn't always work but maybe this time I'll get lucky <g>:

=CHOOSE(WEEKDAY(TODAY()),TODAY()+1,TODAY()+1,TODAY()+1,TODAY()+1,TODAY()+1,TODAY()+3,TODAY()+2),"Sn","Mn","Tu","Wd","Th","Fr","Sa")
& TEXT(TODAY()+1,"\.mmm.dd.yyyy")

 

[StargateFan]

You want to keep the formula unchanged, but get a different result. That's funny.
Here is what I might do...
=CHOOSE(WEEKDAY(TODAY()),TODAY()+1,TODAY()+1,TODAY()+1,TODAY()+1,TODAY()+1,TODAY()+3,TODAY()+2)
Format the cell as: ddd.mmmm.dd.yyyy
'---
Jim Cone
Portland, Oregon USA
http://blog.contextures.com/archives/2011/07/18/find-last-row-with-excel-vba/
(workbook with "universal" Last Row function code - free)





"StargateFan" <[email protected]>
wrote in message

Jim, going cuckoo here. Everything I've tried to get my customized
format results in an error. Nothing I've tried worked. As always,
I've googled and googled (couple more hours for today to add to the
count) but am no closer to a solution.

On this page, I found a shorter formula which I hoped I'd be able to
add my custom day formatting to
(http://en.allexperts.com/q/Excel-1059/2008/6/return-DATE-1-excluding-1.htm)
since it might be easier for me to modify:

=A1+1+2*(WEEKDAY(A1)=6)

changed to meet my cell reference: =B2+1+2*(WEEKDAY(B2)=6)

but I just get #VALUE!

Your formula works but it gives me the standard ddd.mmmm.dd.yyyy
format.

Can anyone direct me to a function, perhaps, where I can change the
weekday display to my "Sn","Mn","Tu","Wd","Th","Fr","Sa"? Since there
doesn't seem to be a way to get this to work via a formula that I can
find, perhaps there is a way to change the settings in Excel? That
would make my life much easier if the days of the week always
displayed as Sn, Mn, Tu, Wd, Th, Fr, Sa.

Thanks.

 

[GS]

This will give you the standard 3-letter abbreviation for a weekday:

=TEXT(WEEKDAY(A2),"Ddd")
Results: "Sun,Mon,Tue,Wed,Thu,Fri,Sat"

You could truncate that to a 2-letter abbreviation like this:

=LEFT(TEXT(WEEKDAY(A3),"Ddd"),2)
Results: "Su,Mo,Tu,We,Th,Fr,Sa"

HTH

 

[Ron Rosenfeld]

I have a very specific formula that I need to keep the same. However,
I need to make the output come out as a weekday at all times. How can
I do this pls?

=CHOOSE(WEEKDAY(TODAY()+1),"Sn","Mn","Tu","Wd","Th","Fr","Sa") &
TEXT(TODAY()+1,"\.mmm.dd.yyyy")

Thanks so much!! D

You can use the WORKDAY function.

=CHOOSE(WEEKDAY(WORKDAY(TODAY(),1)),"Sn","Mn","Tu","Wd","Th","Fr","Sa")
& TEXT(WORKDAY(TODAY(),1),"\.mmm.dd.yyyy")

In versions of Excel prior to 2007, if you get a #NAME! error, look at help for the WORKDAY function for instructions as to installing the Analysis Tool Pak

 

[StargateFan]

You can use the WORKDAY function.

=CHOOSE(WEEKDAY(WORKDAY(TODAY(),1)),"Sn","Mn","Tu","Wd","Th","Fr","Sa")
& TEXT(WORKDAY(TODAY(),1),"\.mmm.dd.yyyy")

In versions of Excel prior to 2007, if you get a #NAME! error, look at help for the WORKDAY function for instructions as to installing the Analysis Tool Pak

Thanks, that's good to know re the Analysis Tool Pak.

Will give the formula a try, thanks! I spent hours on the weekend
trying to figure out how to do this. Thanks again for everyone's
help. Excel gets us out of tight spots! <g>