Forum Home
Press F1
Thread ID: 65810	2006-01-31 21:04:00	Calc time diff in Access	user (1404)	Press F1

Post ID	Timestamp	Content	User
425856	2006-01-31 21:04:00	I am trying to create a database in Access (latest version) which involves calculating the difference in starting and finishing times. These times are in the format hh:mm:ss. If I try simply subtracting one from the other, the answer is given in fractions of a day, rather than in the original format. Is there an easy way of doing this, or a function which would convert the day fraction to the required format? I can't find a function which does this in the expression builder. A search on the web came up with an answer which involved visual basic to achieve this but I have no experience in using this. Any help appreciated.	user (1404)
425857	2006-01-31 21:43:00	I don't know if it will be of any help or not but the format properties I use in Excel is h:mm for the start and finish values and [h]:mm for the sum.	FoxyMX (5)
425858	2006-01-31 21:50:00	I don't know if it will be of any help or not but the format properties I use in Excel is h:mm for the start and finish values and [h]:mm for the sum. I have been able to do the calculation in Excell (using hh:mm:ss) but the database is easier to set up for data entry so I would like to do it in Access. Thanks.	user (1404)
425859	2006-01-31 22:18:00	Ah... well I assumed that Access was the same as Excel but it obviously isn't. Sorry, I can't help any further then but there is bound to be someone else who can so sit tight.	FoxyMX (5)
425860	2006-01-31 22:48:00	Hi! Not sure if you are using queries or forms to do this calculation. If you are using a form you can set the start and end time fields' formats to "long time". To work out the difference you can use a calculated field (in a text box)containing the formula =[endtime]-[starttime] then set the format of the text box to hh:nn:ss (where "endtime" and "starttime" are the names of the time fields you want to calculate the difference to). Hope this helps! :)	darnit (9722)
425861	2006-01-31 23:22:00	Thanks, I was using a query to try to achieve this since the finish time is put in at a later time and I thought it would be easier to input via the datasheet view rather than a form. Can a field be created in the tables using a formula? Trying to do this in the query meant I could not format the field in the required format (it did not offer the long time option). Hope this makes sense.	user (1404)
425862	2006-02-01 01:36:00	The best way to do this is to use the DateDiff function in a query, e.g. =Datediff("n", [time1], [time2]) Have a look here (msdn.microsoft.com)for the full details and options on what difference to calculate (n = minutes, s = seconds etc.) You can set the format in the query by using the format function (see here (msdn.microsoft.com)). But consider also that if you have calculated a time difference then what format do you want to show it in? You don't want to store the time difference in a table because then you will be faced with issues of data maintenance. HTH, Andrew :)	andrew93 (249)
425863	2006-02-01 02:01:00	An example of your own custom format showing the difference in minutes and seconds could be something like this : TimeDiff: Int(DateDiff("s", [time1], [time2])/60) & ":" & Format(DateDiff("s", [time1], [time2]) - 60 * Int(DateDiff("s", [time1], [time2])/60), "00") or even something like this: TimeDiff2: DateDiff("n",[time1],[time2]) & ":" & Format(DateDiff("s", [time1], [time2]) - 60 * DateDiff("n", [time1], [time2]), "00") There's many more ways to do this - it just depends on what you want. Andrew :)	andrew93 (249)
425864	2006-02-01 02:19:00	Thanks Andrew, looking at the links, I came up with Expr2: Format([Endtime]-[Starttime],"h:n:s") which puts the time difference into hh:mm:ss as required.	user (1404)
425865	2006-02-01 03:00:00	Is it likely that the two times will be on different days? A	andrew93 (249)
1 2