Calculate time between dates in PARTIAL days or WORKING HOURS?

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

Hello Charles,

You can try below form
https://roberts.knack.com/farmers#calculateworking-hrs/

if that works for you then I will show you a screenshot of how to achieve this.

Regards,
Sunny Singla
ssingla1985@gmail.com

1 Like

Look like there is an issue with this.

You can try now.

Regards,
Sunny Singla

Sunny,

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.

Outlook-04dnigqz.jpg

Hello Charles,

Yes, sure I’m interested in any knack-related work.

Regards,
Sunny Singla
ssingla1985@gmail.com

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.

You can tell me about how you might do the work?

Outlook-04dnigqz.jpg

Sure
you can all me on
+919855089359

Thanks Sunny! I will call you in a few minutes. One additional question:

Can you get the netWorkDays or a variation to calculate a single day or half day instead of ZERO days?

When I enter 03/03 as the start and 03/03 as the end it calculates as ZERO instead of one day

when you entered same day but time difference then i calculate hours difference + like same day 0 days+
8am to 5 pm = 9 hr

Regards,
Sunny Singla

we can also change in a formula like if there is same-day then we can show 1 days instead of 0

Regards,
Sunny Singla

Currently we do not capture the time, but that could work

That would be helpful

if we don’t want to capture time then it’s easy for us to calculate only days.

Hi Sunny, Can you please share the formula you used to calculate the total hours?

Thank you in advance,
Nathaniel

Hello Nathaniel,

It’s a little complicated. Attaching screenshots

StartDateonly and EndDateOnly contain the Date field only with no time like below


Similarly, StartTimeOnly and EndTimeOnly contain time only

Other fields


Thanks,
Sunny Singla