Dateiff resutl in hh:mm

[Roelkn]

Hi,



I having the following problem
I have a Table. Whit two Fields named "StartTime" and the other named "EndTime" the date notation dd-mm-yyyy hh:mm:ss

Now I want to callculate the time difference between the StartTime and EndTime. I Now that I can use the function DateIff only the result that I'am getting is inn hours of Minutes or Seconds.
I would like it to be hh:mm.

Hope somone can help solving this problem.

Greetings Roel Knippen

[isladogs]

Replace your existing expression with Format(expression, "hh:nn")

Minutes are nn to distinguish from months

[June7]

The difference of dates is just a number - a quantity of elapsed time units, not a date/time value. The Format function will not correctly convert this quantity to hh:nn structure.

x = DateDiff("n", StartTime, EndTime)

Int(x/60) & ":" & x mod 60

The result is a string like 20:36, not a number and not a date/time.

If you need to do any additional calcs with the DateDiff() calculation such a aggregate sums, don't convert to a string.

[Roelkn]

Hi Ridder,

Thank's for the Reply
When changing the expression to from: test: DateDiff('h';[StartDatumTijd];[EindDatumTijd]) to test1: Format(DateDiff('h';[StartDatumTijd];[EindDatumTijd]);"hh:nn") the result is 00:00. But when I calculated manual it should be 1 hour annd 30 min.

Hope you can tell me what I am doing wrong.

Thank's in advance

[Roelkn]

Hi June,

Thank's for the reply.
I'am not a Access specialist. I don't understand where I need to enter the values you are saying.


Hope the screenshot will make It more clear

Thank's in advance

[June7]

Post SQL statement instead of images of design view. Switch the designer to SQL View. Copy/paste statement into post.

Use "n" instead of "h" in your DateDiff(), then:

test1: Int([test]/60) & ":" & [test] mod 60


Added more info to my other post while you were posting.

[Roelkn]

Thank's this was the solution. This the SQL statement I used:

SELECT Personeel.StartDatumTijd, Personeel.EindDatumTijd, DateDiff('n',[StartDatumTijd],[EindDatumTijd]) AS test, Int([test]/60) & ":" & [test] Mod 60 AS test1
FROM Personeel;

[Roelkn]

Hi June,

Having another question on the above issue. How can I place the result of the above query in a field in my table.

Hope you can give some advice.

Thank's in advance

[June7]

It is not necessary to save that calculated data to table, just calculate when needed.

However, if you must, doing so would require either an UPDATE sql action or binding form to the query and using VBA behind form to save calculated result to field.