Workday function query

Hi there.

I'm trying to find a way of forcing the output of a formula to be a
workday.

i.e. If the outcome of the formula is a Saturday or Sunday, to deliver
the prior Friday's date

I know I could add an IF statement, but the formula is messy enough as
it is, and I'd rather not confuse the poor souls who will inherit the
spreadsheet from me.

Many thanks
0
10/24/2008 10:36:08 AM
excel 39879 articles. 2 followers. Follow

13 Replies
682 Views

Similar Articles

[PageSpeed] 42

Hi

The following will add -1 day and -2 days respectively, to the date derived 
from your calculation
=your_calc+CHOOSE(WEEKDAY(your_calc,2),0,0,0,0,0,-1,-2)
-- 
Regards
Roger Govier

<MrIainMacleod@gmail.com> wrote in message 
news:7c0526cb-07e5-4ca4-943f-72000b41a6f0@p59g2000hsd.googlegroups.com...
> Hi there.
>
> I'm trying to find a way of forcing the output of a formula to be a
> workday.
>
> i.e. If the outcome of the formula is a Saturday or Sunday, to deliver
> the prior Friday's date
>
> I know I could add an IF statement, but the formula is messy enough as
> it is, and I'd rather not confuse the poor souls who will inherit the
> spreadsheet from me.
>
> Many thanks 

0
Roger
10/24/2008 11:03:51 AM
Many thanks for the imput.

The current formula is as follows:

=IF(ISBLANK(F13),"",IF(J13="Yes",DATE(YEAR(WORKDAY(AP13,-P13-1)),
MONTH(WORKDAY(AP13,-P13-1))-A335, DAY(WORKDAY(AP13,-P13-1))
+0),WORKDAY(AP13,-P13-1)))

Where would I add the elements you've suggested?

0
10/24/2008 11:47:42 AM
Many thanks for the input.

I'm having trouble getting the elements you've suggested to 'fit'
within my existing formula.

It looks like this at the moment:

=IF(ISBLANK(F13),"",IF(J13="Yes",DATE(YEAR(WORKDAY(AP13,-P13-1)),
MONTH(WORKDAY(AP13,-P13-1))-A335, DAY(WORKDAY(AP13,-P13-1))
+0),WORKDAY(AP13,-P13-1)))

Any suggestions?

0
10/24/2008 12:04:55 PM
On Fri, 24 Oct 2008 03:36:08 -0700 (PDT), MrIainMacleod@gmail.com wrote:

>Hi there.
>
>I'm trying to find a way of forcing the output of a formula to be a
>workday.
>
>i.e. If the outcome of the formula is a Saturday or Sunday, to deliver
>the prior Friday's date
>
>I know I could add an IF statement, but the formula is messy enough as
>it is, and I'd rather not confuse the poor souls who will inherit the
>spreadsheet from me.
>
>Many thanks

=WORKDAY(your_formula +1,-1)

--ron
0
ronrosenfeld (3122)
10/24/2008 1:01:19 PM
Brilliant!

Many thanks.
0
10/24/2008 1:09:56 PM
On Fri, 24 Oct 2008 06:09:56 -0700 (PDT), iainmacleod@hotmail.co.uk wrote:

>Brilliant!
>
>Many thanks.

You're welcome.  Glad to help.  Thanks for the feedback.
--ron
0
ronrosenfeld (3122)
10/24/2008 4:44:13 PM
Hi Ron

that's smart!!

-- 
Regards
Roger Govier

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:pnh3g495kehtsjjrv1ss1lundhn92t648p@4ax.com...
> On Fri, 24 Oct 2008 03:36:08 -0700 (PDT), MrIainMacleod@gmail.com wrote:
>
>>Hi there.
>>
>>I'm trying to find a way of forcing the output of a formula to be a
>>workday.
>>
>>i.e. If the outcome of the formula is a Saturday or Sunday, to deliver
>>the prior Friday's date
>>
>>I know I could add an IF statement, but the formula is messy enough as
>>it is, and I'd rather not confuse the poor souls who will inherit the
>>spreadsheet from me.
>>
>>Many thanks
>
> =WORKDAY(your_formula +1,-1)
>
> --ron 

0
Roger
10/24/2008 6:02:00 PM
On Fri, 24 Oct 2008 19:02:00 +0100, "Roger Govier"
<roger@technology4unospamdotcodotuk> wrote:

>Hi Ron
>
>that's smart!!
>
>-- 
>Regards
>Roger Govier

Thank you.  Of course, you have to have the ATP functions.
--ron
0
ronrosenfeld (3122)
10/24/2008 7:36:40 PM
OK, another one building on this issue...

How do I 'force' a formula to return a date for a given day of the
week.

i.e. I am trying to map a process (to return dates for each milestone
based on start or end date) where a given step must take place on a
Thursday.

How do I come up with a formula which returns the Thursday prior, to a
calculated date (if the actual derived date is not a Thursday)?

Trying to make this make sense, without writing a whole essay, but
failing miserably...
0
10/30/2008 11:26:00 AM
On Thu, 30 Oct 2008 04:26:00 -0700 (PDT), iainmacleod@hotmail.co.uk wrote:

>
>How do I come up with a formula which returns the Thursday prior, to a
>calculated date (if the actual derived date is not a Thursday)?
>

In general:

=A1+1-WEEKDAY(A1+1-DOW)

where A1 contains your derived date, and DOW is the day of the week where
Sun=1.

So, in your specific case:

=your_formula+1-WEEKDAY(your_formula-4)
--ron
0
ronrosenfeld (3122)
10/30/2008 12:55:58 PM
Hi Ron

If you are working on 1=Sunday, then shouldn't DOW for the OP be 5, not 4?

-- 
Regards
Roger Govier

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:qibjg4phjh8a5gamftkjce7j08eid4hcb6@4ax.com...
> On Thu, 30 Oct 2008 04:26:00 -0700 (PDT), iainmacleod@hotmail.co.uk wrote:
>
>>
>>How do I come up with a formula which returns the Thursday prior, to a
>>calculated date (if the actual derived date is not a Thursday)?
>>
>
> In general:
>
> =A1+1-WEEKDAY(A1+1-DOW)
>
> where A1 contains your derived date, and DOW is the day of the week where
> Sun=1.
>
> So, in your specific case:
>
> =your_formula+1-WEEKDAY(your_formula-4)
> --ron 

0
Roger
10/30/2008 7:54:09 PM
On Thu, 30 Oct 2008 19:54:09 -0000, "Roger Govier"
<roger@technology4unospamdotcodotuk> wrote:

>Hi Ron
>
>If you are working on 1=Sunday, then shouldn't DOW for the OP be 5, not 4?

DOW *is* 5 in the formula I posted.

>=A1+1-WEEKDAY(A1+1-DOW)

>where A1 contains your derived date, and DOW is the day of the week where
>Sun=1.

>So, in your specific case:

>=your_formula+1-WEEKDAY(your_formula-4)


or, expanded:

=your_formula+1-WEEKDAY(your_formula+1-DOW)

Note that with DOW = 5, 

	+ 1 - 5 = 4

--ron
0
ronrosenfeld (3122)
10/30/2008 11:16:02 PM
Sorry Ron, I was being even more dense than usual!!!

-- 
Regards
Roger Govier

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:7sfkg4594f53d3i9jo9tuq7gdss91f2rb2@4ax.com...
> On Thu, 30 Oct 2008 19:54:09 -0000, "Roger Govier"
> <roger@technology4unospamdotcodotuk> wrote:
>
>>Hi Ron
>>
>>If you are working on 1=Sunday, then shouldn't DOW for the OP be 5, not 4?
>
> DOW *is* 5 in the formula I posted.
>
>>=A1+1-WEEKDAY(A1+1-DOW)
>
>>where A1 contains your derived date, and DOW is the day of the week where
>>Sun=1.
>
>>So, in your specific case:
>
>>=your_formula+1-WEEKDAY(your_formula-4)
>
>
> or, expanded:
>
> =your_formula+1-WEEKDAY(your_formula+1-DOW)
>
> Note that with DOW = 5,
>
> + 1 - 5 = 4
>
> --ron 

0
Roger
10/31/2008 12:01:45 AM
Reply:

Similar Artilces:

If value in cell Then perform function #2
Hi Guys Many thanks for the solutions - they both work! Emm -- emm808 ----------------------------------------------------------------------- emm8080's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1185 View this thread: http://www.excelforum.com/showthread.php?threadid=27483 ...

Merging from a query
I have a database I am working on that has a bunch of addresses in it. I need to be able to select a chunk of dates to be sent to word for a mail merge. I need a way to pass the date criteria data (from a message window) to a query to get the right records, and send the data the query returns to word in by activiating one click event. What is the best way to do this? ...

TEXT Function: please help
I have a sheet that keeps track of MAC to IP adresses. I've created a formula that will output the required format for DHCP server import. The formula was quite long and it was suggested to use the TEXT function . Thanks to Bob Phillips and my friend LavaDude my rather legthy formula has been reduced to the folowing, but it doesnt work correctly. =IF(D2="","","host"&L2&IF(C2="","",".")&IF(OR(B2="",B2>30,),"",B2&"-")&C2&"{ hardware ethernet "&TEXT(D2,"...

Append query bloat
I have an access database where I use an append query quite often. I will use this same query over and over by changing the table I'm appending from, then I save the query. During the save process, the database bloats from ~50KB to ~150KB. Our company recently upgraded to 2007 and that is when I noticed the problem. Is there a way to stop this from happening? I can manage it by compacting and repairing after each save, but recently I added 4 tables in one day, so this was a pain. Thanks Don't even worry about it. An Access file can be up to 2 GB in size. 150 KB ...

Query Problem 03-27-10
Hi, I have 2 tables, tbBaby and tbVaccineInjection. tbBaby stores the babies' personal data like Name, BirthDate etc., tbVaccine stores VaccineName, DoseNumber (1st, 2nd, 3rd, etc.) and InjectionDate, etc. These 2 tables are one to many in relation, because each baby may have several vaccinations on different dates. I'd like to build a query to retrieve babies with their most recent injection date, i.e. only one record for each baby with latest injection date. my query is as follow SELECT tbBaby.*, tbVaccine.* FROM tbBaby INNER JOIN tbVaccine ON tbBaby.ID=tbVaccine.B...

Does SQL Run Behind Queries?
Access 2007 When I run a query does Access, "go away" and run the SQL for what I see in the GUI? If the answer is, "yes" is there anything that I can switch on to see the SQL that it is running? What I would like to do, if it is possible, is to see the query in the GUI and, at the same time, see the SQL that is being used to select the data from the database; that way I can understand things (exactly what is happening) more clearly and more quickly. TIA for any replies. trip_to_tokyo wrote: > Access 2007 > > When I run a query does...

I have a question about using a function in a form
I have used the "workingdays2" function in a query to calculate the number of working days between two days like so... WorkingDays2([SLA_Date3],[SLA_Date4]) I have tried using this in a text box in a form but the calculation won't work. Any ideas? Another stupid moment. Date4 was earlier than Date3, duh! "SteveM" wrote: > So I assume WorkingDays2() is a user-defined public function? > SLA_Date3 and SLA_Date4 are fields on your form? > > Try putting = sign first: > > =WorkingDays2([SLA_Date3],[SLA_Date4]) > > Steve > > &quo...

Database Query #4
I have created a data table in a worksheet and named it “data”. Then I created a separate worksheet, in the same workbook, and created a database query of the data table, by using Data…Import External Data…New Database Query…Excel Files* and defining the database name by browsing to the same workbook. The query worked fine. However, when I moved the workbook (which included the data source) to a different folder and tried to refresh the database query, I got the following error message: “[Microsoft] [ODBC Excel Driver] The Microsoft Jet database engine could not find the object ‘data’...

DNS clients Does not query the secondary DNS
Hi, I have 2 w2003 Dcs - both DNS servers Primary and secondary. I have Clients running XP static Ip and in both DNS servers IP is configured as Primary and Secondary DNS. My Primary DNS server went down, all clients are not quering the secondary! using NSLOOKUP it showed that clients are still querying the primary with a tomeout error !! all name resoultiond stoped in the organization! any idea? tx NsLookup does not automatically fall through to alternate DNS servers, it is not a good way to test server redundancy. If you ping a host, which will use the DNS Cl...

Combined Functions
Hello, I am trying to streamline my work. I am trying to apply two functions to the same data at once. Is this possible? 1. PROPER(A1) 2. TRIM(A1) How would I combine the above two and have the result appear in one cell? Thanks, -- fncuis ------------------------------------------------------------------------ fncuis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26466 View this thread: http://www.excelforum.com/showthread.php?threadid=397792 Good afternoon fncuis Try this: =TRIM(PROPER(A1)) HTH DominicB -- dominicb ---------------------------...

Problem with a Function
I had posted earlier some formulas I am trying to reduce to functions and didn't get any response. I guess it was too hard or maybe can't be done. Anyway, I'm taking a small piece of it and trying things on my own. In a cell I have the following function: =QBRushYds(1, 1) The first argument is week number corresponding to a sheet name and the second arguement is the position of the player corresponding to a cell on the sheet. The function returns an error (#Value): Here is the function definition: Public Function QBRushYds(W, Q) ' Calculates Points for R...

Word 2007 Find/Highlight Function
In Word 2007, the Find function allows you to find and highlight words or phrases in a document. However, saving the document with those 'found' words highlighted does not save the highlighted text. Additionally, if searching for multiple different words in a document, when you search on a 2nd or subsequent word, the 1st or previous 'found' word highlight all disappear. This functionality was possible in Word 2003 and XP environment. Question: In Word 2007, is there a way to highlight and keep highlighted multiple word searches/highlighted words and save the do...

Lookup function doesn't work
I am using a lookup function to compare the results in sheet1 column 1 to sheet2 column 2 and return the results of sheet2 column2. The function seems to work for the first few entries, but then returns the wrong results for some of the other entries. I am using "=LOOKUP(A7,Sheet2!$A$2:$A$16,Sheet2!$B$2:$B$16)" and it is copied down a column of text/numeric entries in the first sheet. Has anyone else had problems with this? Thanks. -- Frank L Are the values in the lookup_vector i.e. "Sheet2!$A$2:$A$16" in ascending order ? -- Rgds Max xl 97 -- Please respond, in new...

Calculate difference in rows in a query
I have a query that calculates how much revenue is claimed by month per job but then I have to take the new month less the prior month. The problem is the data is in row format. I don't know how to subtract February from January, March from but don't know how. I know how to do it in Excel but not Access. Please help... Example: Order Month JTD Clm Variance 101026521 January $511,525 $0 101026521 February $511,525 $0 101029438 January $1,238 $0 101029438 February $3,713 $2,475 101033168 January $21,465 $0 101033168 February $51,460 $29,995 101034011 Janu...

How to use colmn headings in paramter query
Sorry if this has been asked before, but I am not able to locate it if it has. But here is my delima: I need to be able to select the salesperson sales based on the month (1-12) from table 1. I* can create a parameter query, but am not sure hwo to query onlya certain month. table1 ID salesperson 1 2 3 4 5 6 7 8 9 10 11 12 1 joe 12 0 0 2 12 0 1 1 5 7 8 10 2 steve 2 5 6 18 0 0 2 3 4 11 12 1 select * from table1 where salesperson =joe and .... that is where I get stuck. I need to be able to select a column he...

Preserving formulas when using sort function
Hi If I have a two columns of data say, A and B and the are added together in rows in Column C eg A1 + B1 = C1 A2 + B2 = C2 etc If I then move the contents of cell A1 and put it in say A23 the value of C1 does not change because the C1 become A23 + B1. However, if I sort Column A then the value of C1 changes and the formula appears to be wrong because it now takes the new value of cell A1 How can I make sure that when the sort takes place that wherever the value in A1 ends up it is still added to B1 to create a value in C1? Any help appreciated Hi Andy, Why aren't you moving th...

Extracting invoice data from GP 9 using a SQL query (ODBC)
I'm working to extract invoice detail information from a client's GP 9 MSSQL database, with the goal of being able to mock up their invoices in external systems. Just to keep things interesting, they aren't using GP to it's maximum ability - quotes are generated from another software package, and when converted to a sale, minimal data is entered into GP and processed (such as shipping address, job name, job number, PO number, and occasionally a line it of what appears to be free-form text. I have hard copies of several invoices, various web resources, and access to t...

VBA Function to explode string
Hi. I am wanting to know hbow I would go about getting the following 3 cells: Cells A1, A2, A3: :: 1-4,6,8 50 S1B1:: Into a form that looks more like this ::A1 A2 A3 1 8.33 S1B1 2 8.33 S1B1 3 8.33 S1B1 4 8.33 S1B1 6 8.33 S1B1 8 8.35 S1B1:: How it does the rounding (and on which one), it does not matter! Thanks, Tom -- tomjermy ------------------------------------------------------------------------ tomjermy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24666 View this thread: http://www.excelforum.com/showthread.php?threadid=382414 You haven't exp...

Query Does Not Count "2" Records
Good Evening Everyone... I have a bit of a logical puzzle that I need to solve. I have various queries which have various criteria for them. One query involves retirees or spouses being under the age of 65 and the other involves retirees or spouses being over the age of 65. Both the retiree and spouse are listen in the same records and on one line of the master table and queries. The situation that I am running into is that I can have a retiree who is over 65 and a spouse under 65 and the record comes up twice in the two different queries b/c it meets the criteria, which is perfect. What...

Web Query Help...
If I try and use a web query with the following site http://www.sportsline.com/mlb/stats/playersort/regularseason/yearly/MLB/P I get a message that says the web query returned no data. I click on the arrow right by player and it highlights all the players names and their stats. Anyone know why it doesn't import the data into excel? Is there too much data? Thanks Works fine for me, although one have to do some editing since it imports some trash as well (I would import it, then select the table itself from the import and copy and paste into a new sheet, then import the next 50 and...

Rand() function
Excel spreadsheets using the rand() function in Excel 2000 are not behaving the same way in Excel 2003. The distributions that were "almost-normal" are now significantly non-normal. Does anyone have a pointer to how to fix this problem ? Perhaps: The RAND function returns negative numbers in Excel 2003 http://support.microsoft.com/default.aspx?scid=kb;en-us;834520 Otherwise, RAND() in XL03 is *far* more "almost-normal" than XL00 or XL02. Description of the RAND function in Excel 2003 http://support.microsoft.com/default.aspx?scid=kb;en-us;828795...

I need to compose a ticket for a school function.
http://office.microsoft.com/en-us/results.aspx?Scope=TC&Query=ticket -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "nikk" <nikk@discussions.microsoft.com> wrote in message news:1BB843C2-C773-4DCF-AB1F-617DE90F380B@microsoft.com... > ...

Formatting Result of Custom Function
We are trying to format the result of a custom function. Is this possible. The code below does not do this. Here we are trying to format the letters "cde" in the "test" function below using Macro1 Function test() As String Application.Volatile test = "abcdefgh" End Function Sub Macro1() On Error GoTo iErrors With ActiveCell.Characters(Start:=3, Length:=3).Font .Name = "Calibri" .FontStyle = "Regular" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False ...

COUNTIF Function 04-19-10
I am trying to use the countif function by rows and it is not calculationg correctly. I try to drag the formula down and it just repeats the same value as the origin cell. Is there some type of malfunction with tis function? It would help immensely if you had posted your formula for us to evaluate. Without it, my best guess would be that your formula currently looks something like: =COUNTIF($1:$1,"My value") when it should be: =COUNTIF(1:1,"My value") -- Best Regards, Luke M "csmith13" <csmith13@discussions.microsoft.com> wrote in me...

Fetch XML to Query Expression
I'm trying to convert the following Fetch XML statement to a Query Expression to post using the RetrieveMultiple webservice: <fetch mapping="logical"> <entity name="role"> <attribute name="name" /> <link-entity name="systemuserroles" from="roleid" to="roleid"> <filter> <condition attribute="systemuserid" operator="eq-userid" /> </filter> </link-entity> </entity> </fetch> I haven't been able to find an documenta...