|
Subject:
|
Calculating numbers of days similar to Excel
|
|
Posted By:
|
klott16
|
Post Date:
|
12/19/2005 1:45:10 PM
|
I am hung up at calculating the number of days that a process will take using the DateDiff function will only calculate the difference between two dates. However, in Excel the formula is (I+H)-G, which works fine, but to do that same or simular process in Access is causing a problem. Any suggestions would be appreciated from the Pros. Thanks...
KEL
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/20/2005 7:30:11 AM
|
DateDiff("d", [StartDateField], [EndDateField])
Look up DateDiff or date calculations in the Help box, too.
HTH
mmcdonal
|
|
Reply By:
|
klott16
|
Reply Date:
|
12/23/2005 4:48:27 PM
|
Hi mmcdonal,
I got the DateDiff formula however the calculation they are using involves the 3 dates ex.
((I+H)-G) which is the formula used in the excel spreadsheet calculation, which is going to calculate the total number of days it took to complete the process. However in Access the DateDiff function only takes two dates in the calculation which is the DateDiff formula. I need to calculate the number of days (I+H)-G.. if it were valid it would look something like this... DateDiff("d",([Date Request]+[Date Rec'd]),[Date Rec'd in House]). Access will take the formula but the output is null.
I need a way to add the time for the first two days, then take the difference in total time.
Driving me batty with this one.. it was simple when they only had two dates...
Thanks..
KEL
quote:
Originally posted by mmcdonal
DateDiff("d", [StartDateField], [EndDateField])
Look up DateDiff or date calculations in the Help box, too.
HTH
mmcdonal
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/27/2005 7:19:04 AM
|
Can you explain more about the relationships between the dates?
You have a date a request is made (Date Request)
A date something was received (Date Rec'd)
And another date something was received somewhere else (Date Rec'd in House)
What are their relationships? What date are you subtracting that last field from?
BTW, this is not a good naming convention since there are spaces, and the first single word is a reserved name, and there is a "'" in the field name.
mmcdonal
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
12/27/2005 3:42:51 PM
|
Your DateDiff would not be this
DateDiff("d",([Date Request]+[Date Rec'd]),[Date Rec'd in House])
but it would be this, a combo of DateDiff AND DateAdd:
=DateAdd("d", DateDiff("d",[Date Request],[Date Rec'd]), [Date Rec'd in House])
The embedded DateDiff function would return the number of days between date request and date received and then add that integer via the DateAdd function to the date received in-house to give you a final date.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
anukagni
|
Reply Date:
|
12/30/2005 4:47:02 AM
|
Hie Greg,
Whould u able to sent me an Exmpl for the above said.,
I am fumbling Here...
P.A.P.Raguv,
Executive Manager ,Planning Warehouse Logistics ,
P.Anukprasanna,
Integrety Logistics Executive,
|
|
Reply By:
|
klott16
|
Reply Date:
|
1/3/2006 11:32:21 AM
|
Hi! Hope all had a good holiday! Now back to the grinding stone. To attempt at explaining what I have are three Dates, (Date something was Requested, Date Something was sent for approval, and the Date something was originally rec'd.) As I stated the Excel formula is: (Date_Sent + Emerg_Date_Requested)- Date_Rec'd. The formula is suppose to track the total time the process takes and give the number of days it took total. So in all the Last field should be subtracted from the total time of Sent+Requested which should give the number of days for a process.
Now I thought of another way to do it after analyzing the information more and thats probably putting into a procedure using If, Then, Else.
Ex.: If [Emerg_Date_Requested]= "", then [Date_Sent]-[Date_Rec'd], Else [Emerg_date]-[Date_Rec'd].
I appreciate all and any help you might offer.
quote:
Originally posted by mmcdonal
Can you explain more about the relationships between the dates?
You have a date a request is made (Date Request)
A date something was received (Date Rec'd)
And another date something was received somewhere else (Date Rec'd in House)
What are their relationships? What date are you subtracting that last field from?
BTW, this is not a good naming convention since there are spaces, and the first single word is a reserved name, and there is a "'" in the field name.
mmcdonal
|
|
Reply By:
|
kindler
|
Reply Date:
|
1/3/2006 1:43:56 PM
|
I'm just curious, that Excel formula gives you the number of days the process took? It would seem to me that based on the three numbers all being dates, that adding two and subtracting one that are all more or less equal.. would result in just getting a date somewhat close to the others. Perhaps I'm just confused since the variable names do not seem to equate to the definitions given, and that the sent/rec'd parties are not clearly ID'd.
|
