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).