If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Diff Function
Hi,
I have two columns. each column has a date and a time. i.e. a date when the ticket was opened and a date when it was closed. I want to a third column to automatically tell me the difference in hours between the other two columns. This is what I am trying to use and it is not working. DateDiff("h","Start Time","End Time") Start Time and End Time are the names of my two columns I want to calculate the difference between. I have the calculated column in the format of number. What should I do differently? Thanks |
#2
|
|||
|
|||
Date Diff Function
Maybe I am not understanding the fundamentals of Access yet...Should I
be doing this with a query and just be storing the raw data in the table? |
#3
|
|||
|
|||
Date Diff Function
Okay I just did it in a query...It is giving me a decimal number
though and I tried to group it by system like this: GROUP BY: Manager But I don't think it worked cause I am seeing multiple instances of the same thing... |
#4
|
|||
|
|||
Date Diff Function
Okay I figured out how to reformat it. I am having the same problem I
have in excel though. It is giving me the hour residual value AFTER the number of days. So it is not giving me an accurate number. i.e. I have Start Time End Time 2007.01.12 14:00 2007.01.16 12:33 It is calculating a difference of 22:33. How can I fix that? |
#5
|
|||
|
|||
Date Diff Function
DateDiff("h",[Start Time],[End Time])
Will give you the number of hour boundaries crossed. For instånce 13:59 to 14:01 will return 1 hour. If you have an extended period and want hours and minutes use dateDiff to get the minutes and then do some math. DateDiff("n",[Start Time],[End Time]) will return the number of minutes so DateDiff("n",[Start Time],[End Time])\60 will give you the hours and DateDiff("n",[Start Time],[End Time]) mod 60 will give you the remaining minutes. Combine them to get a string that looks like hours and minutes. DateDiff("n",[Start Time],[End Time])\60 & ":" & Format(DateDiff("n",[Start Time],[End Time]) mod 60,"00") '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '================================================= === wrote: Okay I figured out how to reformat it. I am having the same problem I have in excel though. It is giving me the hour residual value AFTER the number of days. So it is not giving me an accurate number. i.e. I have Start Time End Time 2007.01.12 14:00 2007.01.16 12:33 It is calculating a difference of 22:33. How can I fix that? |
Thread Tools | |
Display Modes | |
|
|