Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change to formula to make it go to next weekday (i.e., avoid weekend days)?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change to formula to make it go to next weekday (i.e., avoid weekend days)?
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...ith-excel-vba/ (workbook with "universal" Last Row function code - free) "StargateFan" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change to formula to make it go to next weekday (i.e., avoid weekend days)?
On Fri, 12 Aug 2011 08:49:59 -0700, "Jim Cone"
wrote: 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 gave a bit of a laugh, though <g. 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 :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") '--- Jim Cone Portland, Oregon USA http://blog.contextures.com/archives...ith-excel-vba/ (workbook with "universal" Last Row function code - free) "StargateFan" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change to formula to make it go to next weekday (i.e., avoid weekend days)?
On Fri, 12 Aug 2011 08:49:59 -0700, "Jim Cone"
wrote: 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...ith-excel-vba/ (workbook with "universal" Last Row function code - free) "StargateFan" wrote in message ... 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 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-105...xcluding-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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change to formula to make it go to next weekday (i.e., avoid weekend days)?
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change to formula to make it go to next weekday (i.e., avoid weekend days)?
On Fri, 12 Aug 2011 06:08:00 -0700 (PDT), StargateFan wrote:
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","T h","Fr","Sa") & TEXT(TODAY()+1,"\.mmm.dd.yyyy") Thanks so much!! :oD 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change to formula to make it go to next weekday (i.e., avoid weekend days)?
On Sun, 21 Aug 2011 23:02:02 -0400, Ron Rosenfeld
wrote: On Fri, 12 Aug 2011 06:08:00 -0700 (PDT), StargateFan wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula help: Duration Without Weekend Days | Excel Discussion (Misc queries) | |||
Calc occurrances for weekday omitting weekend data | Excel Worksheet Functions | |||
if formula returns a weekend - move it forward to next weekday | Excel Worksheet Functions | |||
Formula to Remove Weekend Days | Excel Discussion (Misc queries) | |||
Change the text format for weekend days (Sat, Sun) | Excel Worksheet Functions |