dateTime if then formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
I'm trying to calculate a time duration using an if then statement where:
if [Column A] is null then ([Column C] - [Column B])
else ([Column A] - [Column B])
endif
I've tried this formula:
if IsNull([Column A]) then [Column C] - [Column B]
ELSE [ColumnA] - [[Column A] - [Column B]]
endif
I'm receiving a "Formula: invalid type in subtraction operator" error.
Here is what I have for the Columns and Data Types:
Field | Example | Type |
ColumnA | [Null] | DateTime |
ColumnA | 1/15/2020 14:15 | DateTime |
ColumnB | [Null] | DateTime |
ColumnB | 1/15/2020 14:02 | DateTime |
ColumnC | [Null] | DateTime |
ColumnC | 1/15/2020 14:24 | DateTime |
Thanks in advance
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @BonusCup you'll need to use the datetimediff() function rather than just +/-
So try:
IF ISNULL([Column A]) THEN DATETIMEDIFF([Column C],[Column B],'<unit>')
ELSE DATETIMEDIFF([Column A],[Column B],'<unit>')
ENDIF
<unit> is replaced by hours/days/months etc.
There's more information on datetime functions here: link
Hope that helps,
Ollie
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @BonusCup ,
instead of using "-", use DateTimeDiff:
if !IsNull([Column A]) AND !IsNull([Column B]) then
DateTimeDiff([Column B], [Column A], 'minutes') or 'days'/'weeks' ...
ELSEIF !IsNull([Column B]) AND !IsNull([Column C]) THEN
DateTimeDiff([Column C], [Column B], 'minutes') or 'days'/'weeks' ...
ELSE
Null()
ENDIF
Best,
Roland
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@OllieClarke and @RolandSchubert
Thank you both for the solutions. Both worked. Below are the outputs for both:
Since solution#2 was showing negative minutes I used 'abs' for both "DateTimeDiff". Edit is:
if !IsNull([Column A]) AND !IsNull([Column B]) then
abs(DateTimeDiff([Column B], [Column A], 'minutes'))
ELSEIF !IsNull([Column B]) AND !IsNull([Column C]) THEN
abs(DateTimeDiff([Column C], [Column B], 'minutes'))
ELSE
Null()
ENDIF
New result for Solution#2:
Thanks again to both!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @BonusCup the reason for the negative and positive numbers is because @RolandSchubert and I had our columns the other way round within the datetimediff() function. From the link I posted earlier:
DateTimeDiff (<date/time1>, <date/time2>, <units>) Subtract the second argument from the first and return it as a duration
so if <date/time1> is earlier than <date/time2> then the output of the datetimediff() function will be negative, otherwise it will be positive.
I hope that clears things up a bit,
Ollie
