Excel function

Question

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.&nbsp;

sergeibaklan · Answer

JonGuy23934&nbsp;
That could be
=(end_datetime - start_datetime)*24

jonguy23934 · Answer

Thank you&nbsp;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.

sergeibaklan · Answer

JonGuy23934&nbsp;
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.

jonguy23934 · Answer

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!

sergeibaklan · Answer

JonGuy23934&nbsp;
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.

Forum Discussion

Excel function

5 Replies

Resources