Query Date Help W/Avg work days

please note that in order to get the date 6/28/1979 I am using average work 
days each month of 21.75

I have tried to Add just the total days to the date but it's not correct 
because I need it to be based off the 21.75 days per month not actually days.

Total Years of Prior Service	Days	Hire Date	Adjusted Hire Date

7 years 4 months 6 days	2685	6/15/1987	       2/7/1980 (s/b 2/9/80)
1 year 8 months 13 days	620	2/10/2005	       6/1/2003 (s/b 5/27/03)

This is what is being calculated so far once I run the query.
"aldunford" wrote:

> This worked great thanks so much!! I have one more question.
> 
> Now that i have the format I need as 7 years 4 months & 19 days for my prior 
> service. How can I substract that from new hire date of 11/17/1986 to get 
> 6/28/1979?
> 
> I appreciate all your time and help!!
> 
> 
> 
> "Ofer Cohen" wrote:
> 
> > Look at this link on "A More Complete DateDiff Function"
> > 
> > http://www.accessmvp.com/djsteele/Diff2Dates.html
> > 
> > -- 
> > Good Luck
> > BS"D
> > 
> > 
> > "aldunford" wrote:
> > 
> > > I need to create a query that will do the following:
> > > 
> > > these are my fields: resign date, hire date
> > > 
> > > I need to subtract the resign date from hire date but I want it to return 
> > > the answer as years, months & days
> > > 
> > > ie. 11/7/1986 - 6/18/1979 = 7 years 4 months & 19 days
> > > 
> > > is there a way to use the DateDiff function to return years, monts & days?
0
Utf
3/21/2008 5:05:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
811 Views

Similar Articles

[PageSpeed] 53

in terms it would be a total of 261 work days in a year ALWAYS even in leap 
years

"aldunford" wrote:

> please note that in order to get the date 6/28/1979 I am using average work 
> days each month of 21.75
> 
> I have tried to Add just the total days to the date but it's not correct 
> because I need it to be based off the 21.75 days per month not actually days.
> 
> Total Years of Prior Service	Days	Hire Date	Adjusted Hire Date
> 
> 7 years 4 months 6 days	2685	6/15/1987	       2/7/1980 (s/b 2/9/80)
> 1 year 8 months 13 days	620	2/10/2005	       6/1/2003 (s/b 5/27/03)
> 
> This is what is being calculated so far once I run the query.
> "aldunford" wrote:
> 
> > This worked great thanks so much!! I have one more question.
> > 
> > Now that i have the format I need as 7 years 4 months & 19 days for my prior 
> > service. How can I substract that from new hire date of 11/17/1986 to get 
> > 6/28/1979?
> > 
> > I appreciate all your time and help!!
> > 
> > 
> > 
> > "Ofer Cohen" wrote:
> > 
> > > Look at this link on "A More Complete DateDiff Function"
> > > 
> > > http://www.accessmvp.com/djsteele/Diff2Dates.html
> > > 
> > > -- 
> > > Good Luck
> > > BS"D
> > > 
> > > 
> > > "aldunford" wrote:
> > > 
> > > > I need to create a query that will do the following:
> > > > 
> > > > these are my fields: resign date, hire date
> > > > 
> > > > I need to subtract the resign date from hire date but I want it to return 
> > > > the answer as years, months & days
> > > > 
> > > > ie. 11/7/1986 - 6/18/1979 = 7 years 4 months & 19 days
> > > > 
> > > > is there a way to use the DateDiff function to return years, monts & days?
0
Utf
3/21/2008 5:19:00 PM
On Fri, 21 Mar 2008 10:05:01 -0700, aldunford
<aldunford@discussions.microsoft.com> wrote:

>please note that in order to get the date 6/28/1979 I am using average work 
>days each month of 21.75

Since no month contains 21.75 workdays, and since that is an APPROXIMATION,
you will inevitably be off by at least a day or two. 

Perhaps you could post your existing query and show how you would do the same
calculation manually.
-- 

             John W. Vinson [MVP]
0
John
3/21/2008 7:41:16 PM
So a month (on average) has 365.25/12 days.
A month has (on average per your statement) 21.75 days.

So every work day represents 1.3994253 month days.

Every month day represents 0.71457905 work days.

Beyond giving you the ratios I have no idea to calculate what you want.


'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


aldunford wrote:
> please note that in order to get the date 6/28/1979 I am using average work 
> days each month of 21.75
> 
> I have tried to Add just the total days to the date but it's not correct 
> because I need it to be based off the 21.75 days per month not actually days.
> 
> Total Years of Prior Service	Days	Hire Date	Adjusted Hire Date
> 
> 7 years 4 months 6 days	2685	6/15/1987	       2/7/1980 (s/b 2/9/80)
> 1 year 8 months 13 days	620	2/10/2005	       6/1/2003 (s/b 5/27/03)
> 
> This is what is being calculated so far once I run the query.
> "aldunford" wrote:
> 
>> This worked great thanks so much!! I have one more question.
>>
>> Now that i have the format I need as 7 years 4 months & 19 days for my prior 
>> service. How can I substract that from new hire date of 11/17/1986 to get 
>> 6/28/1979?
>>
>> I appreciate all your time and help!!
>>
>>
>>
>> "Ofer Cohen" wrote:
>>
>>> Look at this link on "A More Complete DateDiff Function"
>>>
>>> http://www.accessmvp.com/djsteele/Diff2Dates.html
>>>
>>> -- 
>>> Good Luck
>>> BS"D
>>>
>>>
>>> "aldunford" wrote:
>>>
>>>> I need to create a query that will do the following:
>>>>
>>>> these are my fields: resign date, hire date
>>>>
>>>> I need to subtract the resign date from hire date but I want it to return 
>>>> the answer as years, months & days
>>>>
>>>> ie. 11/7/1986 - 6/18/1979 = 7 years 4 months & 19 days
>>>>
>>>> is there a way to use the DateDiff function to return years, monts & days?
0
John
3/22/2008 12:17:01 AM
Reply:

Similar Artilces:

Macro Help 11-24-09
I have one workbook of data (1 tab) that has data for 20 different Sales Reps (different names). I need to copy all data for "Rep A" into a separate worksheet, and same for "Rep B" and so on. At the end I would have 1 tab for all data and 20 tabs with the data for each rep. Basically, I need to copy and paste each rep data into a new worksheet within the same workbook but didn't want to do it manually. I hope this makes sense. See Ron de Bruin's site for code. http://www.rondebruin.nl/copy5.htm Also check out his easyfilter add-in. http://www.ro...

Excel 97 VBA Help File
In the MS Excel Visual Basic Reference help file contents page, I click on Functions and it only offers me functions beginning with the letter S. So, I have a list of Solver and SQL functions. But what about all the other functions in VBA, for example for doing arithmetic and manipulating dates and strings? Why don't they show up? Are they left out because those functions are all part of Visual Basic generally, and the Excel VBA help file is specific to the _extra_ functions in Excel VBA? It's the only explanation I can think of. Am I right, or have I got a corrupted help file (vbaxl...

junk mail filter doesn't work with rules?
Hello, I receive mail from many pop3 accounts and mailing lists. I use rules to organize the mail within my mailbox. One problem I am having is that the junk mail filter does not screen mail that is affected by the rules. So for instance, I get an email from some alternate account that I have. The mail comes in and gets picked up by the rule and moved to the appropriate folder. When this happens, the junk mail filter never checks the message. Only mail left in the Inbox is screened. Can someone help figure out how to make this work or does anyone have a workaround? I'd hate to ...

help need with VC 6.0 IDE and mfc
Hello, First let me explain the scenario where i m using this requirement. We are Using CustomAppWizard and designing a wizard .One of the wizard pages will Insert Composite controls as many as the user wants . 1.So i should be able to dynamically insert ATL controls without using Insert Control Dailog. 2. can any one tell me how to dynamically create Template file in TEMPLATE folder of resource view . 3. I want to include many files created by templet files and add them to build by editing newproj.inf Is it possible to do this. 4.I would even like to know if i have 2 ifles in my C drive h...

Help, I cannot Save!
I created a document and locked the worksheet to protect the formulars before creating a template for the document. But now when I open th document and insert a new sheet using the template I created, th document will refuse to save. Once I click on save, office assistant will say "doc not saved". Wha could I have done wrong? PLease help. computerfinema -- computerfinema ----------------------------------------------------------------------- computerfineman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3716 View this thread: http://www.excelforum.c...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

Help Required
Hi, Whenever I open Outlook 2003, I am getting a dialog box which displays the following message: Microsoft Office Outlook has encountered a problem and needs to close. We are sorry for the inconvenience When I click Debug it displays a message box with the following error message "The instruction at "0x3007e993" referenced memory at "0x0000000:. The memory could not be read" When I click No it Visual Studio JIT debugger pops up. I uninstalled and installed several times but still the problem persists. Is there any regsitry entry that I've to modify/delete? ...

HELP Recovering addresses and email from Outlook 2003
I had some serious driver issues that required re-installing XP from disc. I did use the backup option and have a backup of all the old data. And of course had to reinstall Office 2003. Will third party software restore my old email and addresses or am I out of luck?? Thanks for the help texraid wrote: > I had some serious driver issues that required re-installing XP from > disc. I did use the backup option and have a backup of all the old > data. And of course had to reinstall Office 2003. > > Will third party software restore my old email and addresses or am I > out of lu...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

Need Help with Deleting Empty Paragraphs in Word 2003
I have written the code below to delete all empty paragraphs at the end of a document and then place the cursor at the end of the last paragraph. It works fine as a stand alone sub in a new doc, but fails inside the real document that contains other code that manipulates several documents. The failure is that it will delete the last empty para, but then gets stuck looping inside the While...Wend because subsequent .Delete are not happening. So, the question is why would this work in one document, but then fail in another? n = 0 ...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Page setup for 8.5x11 calendar w/picture on tabloid paper?
How can I set up a calendar with 8.5x11 (landscape) pages with pictures for each month to print on tabloid (11x17) paper so it can be saddle stiched? I would like the picture on the top half and calendar on the bottom half. I keep ending up with two of the same pictures on one page and two calendars on the next page. Thanks, Bob I assume you are taking this to a professional printer so why not just set each page up as a landscape 8� x 11 and let your printer do the rest? -- JoAnn Paules MVP Microsoft [Publisher] "Bob at 4799" <Bob at 4799@discussions.microsoft.com>...

Help me identify my missing permission (Cannot open public folder) -2147217843 (Maybe Authentication Fails?)
The following snippet of code throws an error number -2147217843. When I googled this error code, I see many references to authentication failed. I am assuming my problem is some kind of permission related problem on the "MyNewFolder" public folder. -- start code --- Dim objFolder As New CDO.Folder Dim f As ADODB.Field 'sURL is like: file://./backofficestorage/mydomain.com/Public Folders/MyNewFolder/ objFolder.DataSource.Open sURL, , adModeReadWrite, adFailIfNotExists --- end code -- I have code that runs before this that actually creates the "MyNewFolder" publ...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

Ctrl+D not working on outlook messages
Any idea why? alltimefav wrote: > Any idea why? And what are you expecting to happen? "alltimefav" wrote in message news:a95c52df-a387-4a8f-bf9d-269ff485451f@i29g2000prf.googlegroups.com... > Any idea why? So instead of hitting Ctrl+D to delete an item, what happens when the item is selected and you hit the Del key, or the "X" toolbar button, or right-click on the item and select Delete? Did you really expect a detailed response for such a vague question? You didn't even bother to say what "not working" means, like the item does not get delete...

how to make macro work even a sheet is hidden.
hello there I have macro assigned to a button in Sheet 1 which goes to sheet 2. Press a button to Refresh and then PRINT preview It does work in normal state. I do not want the user to see the Sheet 2. I hid sheets from Format-sheet-hide. but the macro is not working when sheet 2 is hidden. how to overcome this error "can't execute code in break mode" the macro code is Sub Print_Preview() Sheets("PaySlips2009-10").Select Application.Run "'Latest 2009Payslip.xls'!Sheet2.HURows" ActiveWindow.SelectedSheets.PrintPreview ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

sort by day of the week?
Excel 2002 I have 10 years of grain prices...one price for each trading day of th year. In 'A' we have the date, in 'B' the price. What I need to do i get a list of prices for Mondays, Tuesdays, Wednesdays, etc... Possible? (I really, really don't want to do this manually) Than -- Message posted from http://www.ExcelForum.com add a column = weekday(a1,1) etc and do a sort on this colum -- Message posted from http://www.ExcelForum.com Thank you. You saved me 3 or 4 hours of excruciating data entry. Thi was the last step of a vital report. Now I can do my char...

Symbol Updating Only Every Few Days (if at all)
Using Money 2006, and have a symbol "VLO" that is only updating every few days. This stock was a duplicate (downloaded transaction created a new version of the same stock - my fault not clicking correct choice when asked). I've removed the symbol from the stock entry that was downloaded, renamed this entry to something bogus, deleting this renamed stock "from all accounts", then added the symbol back to the original VLO stock that I've been tracking for years. Now the stock just says "unch" in the portfolio view, and the price history is only updat...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...