Calculating # Work Hours between 2 Date and Time Stamps

1

I’ve got 3 different columns.
Column 1: Start Date and Time
Column 2: Finish Date and Time
Column 3: # Work Hours between Column 1 and Column 2

How can I calculate Column 3, assuming work hours are 9AM to 5 PM weekdays?

2

Hey @Tina_Wu, I use the following formula to determine how many minutes are between two times:

round(( minute(thisRow.[End]-thisRow.[Start]) +hour(thisRow.[End]-thisRow.[Start]) *60 ),1)

You could divide by 60 to get hours. You can add more logic if you want to filter out times before 9am or 5pm. This should get you started. Good luck!

3

What kind of logic would you apply to only include times between 9 AM to 5 PM ?

4

It depends on what you want to do and how you are collecting the data. Are you trying to prohibit any entries before or after? Are you wanting to only count the time after 9am (ie someone clocks in at 850 but you want it to only consider 9+).

You need to make a doc and start building. There are too many possibilities to contemplate with your limited constraints.

5

Hi @Tina_Wu,

Welcome to the Coda Community!

This is kind of a tough one to answer. There are a few things at play which need to be calculated and there are probably several ways to do this. Here’s something I drafted up that might do what you need. It is a little involved, but looks to work for all situations that don’t include holidays. This is strictly a weekday strategy. It might take some more time before I find a better solution.

I’m curious if anyone else has other solutions. I think this can be improved upon.

1 Like
6

My logic would be the same (calc # hours before first day end; since last day start; and add the rest of the days X number of hours in a day). One improvement I’d think about is using NetWorkingDays() instead of manually FormulaMapping/Filtering for weekends.

1 Like
7

Hi,

I am working on something similar which was adapted from NetWorkDays with Excel. It works for normal monday-friday but it becomes challenging when there’s a need to customize weekends (working saturdays for example). Excluding public holidays is also possible.

Here’s the link to the excel formula i used: https://exceljet.net/formula/get-work-hours-between-dates-and-times

In coda, i replaced networkdays with networkingdays. and i created multiple columns to help with the mini-formulas (like mod).