 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008date and time [skipping weekends]
I have a number in a cell that represents days. I wish to add these days to the current date and time, which is easy to do. =NOW()+A3 I format it into time displaying date and time. How can I automatically skip the weekend from Friday 16:00 to Sunday 16:00 which is 48 hours in the addition? Try: =NOW()+A3+CHOOSE(WEEKDAY(NOW()+A3,2),0,0,0,0,0,2,1)  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I have a number in a cell that represents days. I wish to add these > days to the current date and time, which is easy to do. =NOW()+A3 > I format it into time displaying date and time. > How can I automatically skip the weekend from Friday 16:00 to Sunday > 16:00 which is 48 hours in the addition? This worked very well. Thank you! The reason I wanted this function is I trade forex and my spreadsheet formulas suggest trading time frames which do not include weekends, therefore I need to skip weekends as there is no trading. So, for example, if a signal came on a Thurs. indicating that a change in 3 days could occur, under normal counting that would be on Sunday. I have to skip weekends, therefore the signal should come on Tues. Anyways, it works well and gives a better idea without having to look at a calendar. Thanks again. > Try: > [quoted text clipped - 15 lines] > > How can I automatically skip the weekend from Friday 16:00 to Sunday > > 16:00 which is 48 hours in the addition? This worked very well. =NOW()+A3+CHOOSE(WEEKDAY(NOW()+A3,2),0,0,0,0,0,2,1) > This worked very well. Thank you! > The reason I wanted this function is I trade forex and my spreadsheet [quoted text clipped - 30 lines] > > > How can I automatically skip the weekend from Friday 16:00 to Sunday > > > 16:00 which is 48 hours in the addition? Hi Rhince, Although Sandy's formula will in some respect skip some weekends, I would advise some caution with it. Since you have explained your situation a little more clearly now, testing the formula with your exact example (Thurs + 3 working days) actually gives me Monday the following week (only 2 days by me way of thinking), as will adding 2 days or four days in fact. Just a word or warning. Cheers, Ivan. > This worked very well. > [quoted text clipped - 36 lines] > > - Show quoted text - >I have a number in a cell that represents days. I wish to add these >days to the current date and time, which is easy to do. =NOW()+A3 >I format it into time displaying date and time. >How can I automatically skip the weekend from Friday 16:00 to Sunday >16:00 which is 48 hours in the addition? Please specify you problem with more details. What do you mean by "skip the weekend ... in the addition"? Do you want the result to be "rounded" up to Sunday 16:00 if the result of NOW()+A3 falls within the "weekend"? Or do you want to just add 2 days to the result in that case? And what if A3 is more than 7 (one week), e.g 50? Do you want to add 2 days for each weekend covered by the A3 days? / Lars-Åke Hi, This is assuming that you want to add x days to the date in cell A5 (replace with Now() if necessary) and assuming that number of days to add is in cell D5. It also assumes that I understand your question correctly... as I see it you want to add a number of days and skip and weekends. ie. If it's Thursday and you want to add 4 days, you would expect the result to be Wednesday the following week and so on. =A5+IF(D5>5-WEEKDAY(A5,2),INT((D5+WEEKDAY(A5,3))/5)*2+D5,D5) There is a good chance that this can be optimised... haven't looked at that yet. The one thing to be aware of is that this formula will glitch if the date you are adding to is in fact a weekend... in which case you might need to wrap another if around it to test for that. Cheers, Ivan. On Mar 30, 9:20 am, Lars-Åke Aspelin <lar...@REMOOOVE.telia.com> wrote: > >I have a number in a cell that represents days. I wish to add these > >days to the current date and time, which is easy to do. =NOW()+A3 [quoted text clipped - 12 lines] > > / Lars-Åke Upon further scenarios, this formula worked the best. =A5+IF(D5>5-WEEKDAY(A5,2),INT((D5+WEEKDAY(A5,3))/5)*2+D5,D5) > Hi, > [quoted text clipped - 35 lines] > > > / Lars-Åke Give this function a try... =WORKDAY(NOW(),A3) Note 1: If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Note 2: Also check out the help files for this function as there is an optional 3rd argument which can allow you to skip over holidays as well. Rick >I have a number in a cell that represents days. I wish to add these > days to the current date and time, which is easy to do. =NOW()+A3 > I format it into time displaying date and time. > How can I automatically skip the weekend from Friday 16:00 to Sunday > 16:00 which is 48 hours in the addition? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.