DateDiff function for a Calculated field

[saledo2000]

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]

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

 

[June7]

Cross post https://stackoverflow.com/questions/62176742/datediff-between-mydate-and-system-date

Why not save Now() then calculate DateDiff in query?

 

[strive4peace]

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]

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]

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]

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.