Check presense of date/Time field? | IF in DateTime field gives syntax error

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
Hi there QB-community,

I'm trying to build something around timekeeping.
In my design I have 2 date/time fields to get a start DateTime value from the user.
One field is created as a timestamp the other field is created by manual entry.
[StartTime_TimeStamp]
[StartTime_Manual]

Via a third formula date/time field [StartTimeFinal]  I like to use to most preferred start time in further presentation and reporting purposes.

So basically i look for an IF function to set the [StartTimeFinal]
in human logic something like:

IF [StartTime_Timestamp] exists than [StartTimeFinal] = [StartTime_TimeStamp] else
IF [StartTime_Manual] exists than [StartTimeFinal] = [StartTime_Manual]

But using the IF function in a date/time field gives a syntax error "expecting DateTime"

Who can give me some suggestions ?

Thanks Jan-Willem




Photo of Jan-Willem

Jan-Willem

  • 560 Points 500 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
Try this

IF( not isnull([StartTime_Timestamp]), [StartTime_TimeStamp], [StartTime_Manual])
Photo of Jan-Willem

Jan-Willem

  • 560 Points 500 badge 2x thumb
Ah thanks 'not isnull' ... everytime again you learn me something new :-)

about an hour off puzzling brought me to this solution that also seems to work
If(Day(ToDate([StartTime_TimeStamp]))<32,[StartTime_Manual],[StartTime_TimeStamp]).
I must admit yours is less coding and conversion thus possibly better/cleaner

We just finished dinner on my end.. so I continue my quest.. let's see what works best in the end.
I'll let you know.




The goal is to write formulas with the least code yet most understandable for the long term.

Some poor soul years in the future, perhaps even you, will one day need to adjust this formula or see what logics it’s using so you want to have formula very obvious to someone looking at it for the first time.
Photo of Jan-Willem

Jan-Willem

  • 560 Points 500 badge 2x thumb
It works perfect with the 'not isnull' suggestion thanks