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

  • Thread starter Thread starter StargateFan
  • Start date Start date
S

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!! :oD
 
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...
 
You want to keep the formula unchanged, but get a different result. That's funny.
Click to expand...

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
gave a bit of a laugh said:
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
Click to expand...

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 :oP.)


Thanks. :oD

(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")
 
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
Click to expand...

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.
 
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
 
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
Click to expand...

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>
 
Back
Top