I have a strange and specific need to calculate the number of days, partial days, or possibly just “working hours” between two dates (hours if dates and times are provided for the START and END). Right now I use a number of date fields, and only calculate “whole working days” using the netWorkdays() forumla (thanks for that).
Unfortunately if someone starts and completes work on the SAME DAY, it always calculates as 0 days, instead of SOMETHING. This really is not accurate as if a process is started and finished on the same day, did it take ZERO days, or did it take ONE day? Really it would be more accurate in my case for it to show a HALF a day (0.5 days) as it usually has another process either start, or start and finish on that same day (again accumulating ZERO days making time measurement impossible).
One option could maybe be PARTIAL DAYS, meaning I could calculate using a single fractional like 0.5 so if a task was started on 01/01/2022 at 8:00AM, and finished at 11:59AM on 01/03, it could say 2.5 days, as it would be less than half of the 3rd working day (rounded up). Note that in these examples, the DATES listed are all within Monday-Friday.
Any ideas on how I could go about this? Any comments on measuring time or the weird “ZERO DAYS” I get? I tried experimenting with the “round up” and decimal placements, but nothing in DAYS seems to calculate properly using these (it will just read 2.0 days or 0.0 days depending).
Any ideas, folks?
An even more complex example that could be used:
An ideal measurement would really be in WORKING HOURS. For example, the business is open Monday-Friday, 8AM-5PM. I would want to start the opening “START TIME” at whatever time the record is created or the time is selected, say 01/01/2022 @ 12PM. If I I completed the task and logged the “END TIME” as 01/03/2022 @ 10AM, I would want it to calculate at 16 total working hours (5 hours on 01/01, 9 hours on 01/02, and 2 hours on 01/03 = 16 hours).
I have not checked this much, but I am completely impressed at the speed of your response. On the surface it sure looks like it is working for me, and I simply divided the number of worked hours by the number of hours in a “work day” and it gives me the “fractional day” I was talking about as well…! I might have to do a few small things to this but it looks great.
As you are a Knack expert and clearly have some knowledge, I may also have some consulting work to do in Knack if you are interested? Specifically I need to start pushing/pulling records or automate the upload of a CSV for import.
Do you have time for a quick phone call or discussion on this solution, and also some future work? I can give you some quick examples and explain how I am using this.
Could you spare a moment for a brief phone call or discussion about this solution and upcoming work? I can provide quick examples and explain how I’m implementing updates to the age calculator tool.
