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
|
|||
|
|||
finding elapsed time
I have a table that gives two sets of times in date/time
format. I need to find the elapsed time between these two data items AND I need the result to be formatted in total hours:minutes. Using DateDiff I have been able to find the total number of minutes and then divide that number by 60, however that results in the hours and fraction of hours rather than hours and minutes. Any idea on the best way to overcome this problem? Is DateDiff my best option or is there something else? Thanks! |
#2
|
|||
|
|||
You can try this.
Thake an example, say 230 minutes. We know it's 3 hours 50 minutes. 230/60 = 3.833333 subtract the integer portion 3.833333 - 3 = 0.833333 multiply the decimal portion by 60 to get minutes 0.833333*60 = 50 3 hours 50 minutes "Dan" wrote in message ... I have a table that gives two sets of times in date/time format. I need to find the elapsed time between these two data items AND I need the result to be formatted in total hours:minutes. Using DateDiff I have been able to find the total number of minutes and then divide that number by 60, however that results in the hours and fraction of hours rather than hours and minutes. Any idea on the best way to overcome this problem? Is DateDiff my best option or is there something else? Thanks! |
#3
|
|||
|
|||
On Tue, 17 Aug 2004 07:56:15 -0700, "Dan"
wrote: I have a table that gives two sets of times in date/time format. I need to find the elapsed time between these two data items AND I need the result to be formatted in total hours:minutes. Using DateDiff I have been able to find the total number of minutes and then divide that number by 60, however that results in the hours and fraction of hours rather than hours and minutes. Any idea on the best way to overcome this problem? Is DateDiff my best option or is there something else? Thanks! You can do some arithmatic operations: DateDiff("h", [start], [end]) & Format(DateDiff("n", [start], [end]) MOD 60, ":00") John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#4
|
|||
|
|||
Mr Vinson,
Pardon me, but won't that give you incorrect data in many cases? Since going from 06:59 to 07:01 would return 1 hour and 2 minutes vice zero hours and 2 minutes? I think you meant something like the following. Which, IF I recall correctly, I originally got from you. DateDiff("n", [start], [end])\60 & Format(DateDiff("n", [start], [end]) MOD 60, ":00") John Vinson wrote: On Tue, 17 Aug 2004 07:56:15 -0700, "Dan" wrote: I have a table that gives two sets of times in date/time format. I need to find the elapsed time between these two data items AND I need the result to be formatted in total hours:minutes. Using DateDiff I have been able to find the total number of minutes and then divide that number by 60, however that results in the hours and fraction of hours rather than hours and minutes. Any idea on the best way to overcome this problem? Is DateDiff my best option or is there something else? Thanks! You can do some arithmatic operations: DateDiff("h", [start], [end]) & Format(DateDiff("n", [start], [end]) MOD 60, ":00") John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#5
|
|||
|
|||
On Tue, 17 Aug 2004 17:22:36 -0400, "John Spencer (MVP)"
wrote: Mr Vinson, Pardon me, but won't that give you incorrect data in many cases? Since going from 06:59 to 07:01 would return 1 hour and 2 minutes vice zero hours and 2 minutes? I think you meant something like the following. Which, IF I recall correctly, I originally got from you. DateDiff("n", [start], [end])\60 & Format(DateDiff("n", [start], [end]) MOD 60, ":00") EEEP! I clearly needed some more coffee when I posted the first reply. You're exactly correct, of course; thanks for the good catch! John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculating Elapsed Time | Christine | Running & Setting Up Queries | 3 | June 18th, 2004 04:21 PM |
time zone & apt times auto changing | Mary | Calendar | 6 | May 25th, 2004 05:44 AM |
Time function - elapsed hh:mm | Rolls | New Users | 2 | May 4th, 2004 04:00 PM |
elapsed time | dan | Worksheet Functions | 1 | October 18th, 2003 08:13 AM |