Trying to sort out how to create an IF statement based on whether or not a datetime field has an entry … and it is defeating me.
What I’m trying to do:
I have various projects underway, and I’d like to calculated the number of hours so far — but only for the projects for which I have not entered a finish date.
I have two datetime columns, one for “started,” one for “finished,” and of course I can calculate the ongoing time for all of them forever, but I’d really rather not do that. Instead, once a project is finished, I want the “hours so far” column to show a null value.
I did try using a ‘default’ finish date of ‘01/01/2011 00:00’ so I could attempt the following:
IF(
({Finished} < {Started}),
(DATETIME_DIFF({Finished}, {Started}) “hours”),
null
)
But I kept getting the error “unexpected , at character 49.” So, totally lost. Any ideas?
My current workaround: calculate total hours from “Started” until now in a column, “hoursSoFar,” determine whether or not the “Finished” column is empty, returning a 1 or 0 (by asking whether “Finished” is after NOW()). Based on that 1 or 0, where “Finished” is empty, returning the calculated hours from “hoursSoFar.”
Maybe that’s just the best way to do it? Not at all sure.