I am trying to create a measure counting the number of working days between a hire date and termination date using the formula below
NetWorkDays([hireDate],[terminationDate1])
This works for employees with a term date but returns blank when the term date is empty. How can I fix this such that if term date is blank, it uses Today as the end date in this formula?
In general you could use alt() also for such tasks because it returns always the first numeric value which means it's just a matter to apply the n date-values in the wanted order.
A slightly other approach might be to replace alt() with a range-function, maybe like this one:
to define a certain min/max value. Such logic might also use combined and/or nested alt()/range-function approaches and/or including/wrapped by any if-loop-conditions.
Beside the above you may also consider to transfer the essential parts of the logic into the data-model, for example by defining the workingdays within the calendar - as flag-field with just 0/1 values and then a simple:
sum(FlagField)
would in regard to selection state also return the wanted results.
Thank you - that worked great - can I use similar logic with the start date - i.e. either hire date or a fixed point in time - for example first day of current month? In that situation the hire date would not be null or blank.
If I can then I could set up the formula to calculate between the later of start of period and hire date and the earlier of term date and today
In general you could use alt() also for such tasks because it returns always the first numeric value which means it's just a matter to apply the n date-values in the wanted order.
A slightly other approach might be to replace alt() with a range-function, maybe like this one:
to define a certain min/max value. Such logic might also use combined and/or nested alt()/range-function approaches and/or including/wrapped by any if-loop-conditions.
Beside the above you may also consider to transfer the essential parts of the logic into the data-model, for example by defining the workingdays within the calendar - as flag-field with just 0/1 values and then a simple:
sum(FlagField)
would in regard to selection state also return the wanted results.
I had to tweak your range suggestion slightly - using rangemax instead but it works. I am going to revisit the data model suggestion later but for now I have what I need.
