Excel function

Copper Contributor

In my spreadsheet, I have projects' start time/date and end time/date. I need to write an excel function that calculates how many hours were spent on the project that subtracts lunches, breaks, and weekends. 

5 Replies

@JonGuy23934 

That could be

=(end_datetime - start_datetime)*24

Thank you @SergeiBaklan! That's the function I'm using now, and it gives me the total time spent. The problem is I'm having to manually subtract weekends, lunches and breaks from the result.

@JonGuy23934 

I see. Perhaps you could give bit more details how your data is structured. In addition to start/end where and how breaks are defined, where are public holidays list, etc. Ideally with sample file.

The company I work for won't allow me to share a file, but I think you might have solved my problem. I hadn't added columns for breaks, lunches, or weekends. I think if I add in those columns and subtract them in my function, I'll get the data I need.
Thanks for the help!

@JonGuy23934 

Yes, it could work, but depends on HOW do you add such breaks. Is that one or many ones, same for each day or not, etc. You may not share your production file, but if you generate sample one with just 2-3 records (and without sensitive information) it'll be more clear. Without knowing of how data is structured it's hard to suggest any formula.