• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

DateDiff function for a Calculated field (1 Viewer)

saledo2000

Registered User.
Local time
Today, 23:18
Joined
Jan 21, 2013
Messages
94
I am trying to get result in TimeDiff column in my table where I have run DataMacro on TimeDiff column. DataDiff is between MyDate and System date and time.

I would like to have hours stored in my TimeDiff column, but keep getting an error. Could you please help. Thank you.

DateDiff("hh";[MyDate];Now())+(Format(Now();"dd\.mm\.yyyy hh:nn";2;1)<Format([MyDate];"dd\.mm\.yyyy hh:nn";2;1))
 

cheekybuddha

AWF VIP
Local time
Today, 23:18
Joined
Jul 21, 2014
Messages
557
Hi,

The Format() function returns a string, so it's no use for doing date arithmetic.

Do the formatting afterwards.

Is this in an expression or VBA code?

Try it like:
DateDiff("hh"; [MyDate]; Now()) + (Now() < [MyDate])

hth,

d
 

strive4peace

AWF VIP
Local time
Today, 18:18
Joined
Apr 3, 2020
Messages
571
hi saledo,

neither DateDiff nor Format may be used in calculated field expression in the table design. But there are ways around that ... this will calculate the numbers of hours as a whole number with 2 decimal places:

IIf([myDate1] Is Not Null And [myDate2] Is Not Null, Round(([myDate2]-[myDate1])*24, 2), Null)

You also can't use Now() or Date() ...
 

strive4peace

AWF VIP
Local time
Today, 18:18
Joined
Apr 3, 2020
Messages
571
and, btw, saledo, since you can't use Now(), what you CAN do is make another field and set its default value to Now() and use that in your equation ;) ~

I see you're using a Data Macro, not a calculated field expression -- so there is a bit more you can do
 

saledo2000

Registered User.
Local time
Today, 23:18
Joined
Jan 21, 2013
Messages
94
Hi,

The Format() function returns a string, so it's no use for doing date arithmetic.

Do the formatting afterwards.

Is this in an expression or VBA code?

Try it like:
DateDiff("hh"; [MyDate]; Now()) + (Now() < [MyDate])

hth,

d
Hi
This is an expression
 

saledo2000

Registered User.
Local time
Today, 23:18
Joined
Jan 21, 2013
Messages
94
Excellent I've got the result. Thank you all guys. Result is in hours but need to type date and time in my text field in format 01.06.2020. 14:30.
DateDiff("h";[MyDate];Now())+(Format(Now();"dd\.mm\.yyyy hh:nn";2;1)<Format([MyDate];"dd\.mm\.yyyy hh:nn";2;1)).
Just removed one h from formula.
Thank you all.
 

Users who are viewing this thread

Top Bottom