As a Knack Builder, I would like to be able to extract single parts from a Date/Time fields so that I can use (and display) those individual components on particular views.
Scenario: Currently this takes many extractions and workarounds to get this to be set up.
If the “from” date value is after today, then Job is “PENDING.”
If the “from” date value is on/before today AND the “to” date value is after today, then Job is “IN PROGRESS.”
If the “to” date value is before today, then Job is “COMPLETE.”
Also: Currently it’s not possible to use the text formula function formatDate to extract end dates/times and this makes this function difficult to use on all date/time field setups.
Hi all,
I agree that this would be a helpful feature. I have the same problem.
Christopher, thanks for the workaround, I'd been trying to do something similar without success for a while.
When I tried your workaround, I ran into two problems:
1. I want my user to enter dates in Australian format (DD/MM/YYYY) and this caused issues when converting the text formula of start time (field: "extract start time" and "extract end time"), back into a date/time field, because it was reading my Australian date as American (MM/DD/YYYY).
2. In the original date/time field, if the end time is on a different date, the text formulas don't work. This is because, when start time and end time are on the SAME day it displays as this: "01/01/2017 09:00 to 17:00" however when start time and end time are on DIFFERENT days it displays as this: "01/01/2017 09:00 to 02/01/2017 09:00".
For anyone who runs into the same problems, click here for my solution
Hi Stephen,
Thanks for posting this.
I am in full agreeance with you- this would be a very helpful feature.
I get very frustrated by having to decide whether I want to use a date / time field in a calendar or whether I want to be able to use the start and end times to make other calculations, e.g. total duration.
I am sure this affects many other users so hopefully this post will gain traction and Knack will address the issue.
Cheers
Abhinandan
Hi Matt,
Regarding my original post, Chris covers the solution pretty well.
Regarding my other comment about work days, I've added a Feature Request in the following thread: https://support.knack.com/hc/en-us/community/posts/115002191671-Equation-Calculation-for-Work-Days
Feel free to upvote, cheers mate.
Hey Stephen,
I would also like to know the answer to this one - trying to record holiday days for staff employees is a bit of a nightmare as we are having to create separate records at current to ensure Saturday, Sunday and public holidays are not encapsulated in the sum.
Any luck with this one?
Ahh, nice work around.
Regardless, it should be treated as two separate values really.
While we're on the topic, do you have any tips for removing weekends from the difference to calculate only "work days"?
Stephen,
I had the EXACT same challenge. Knack helped me with the following work-around, hopefully it works for you to.
I added a step because I don’t use military time in my work around, so I had to add a “converter” field using conditional values.
its based on using military time on a date/time field, then two text formulas to extract the start and end times. Those would look like this:
And then End Date:
Then for each of those text formulas, you’ll need corresponding date/time fields whose values are set automatically via conditional rule:
And finally, a date equation that works with those two new date/time fields:
1 Like
