Formula for defining time spent in specified time periods

**SuneLolk** · 03-19-2019, 10:20 AM

Hi all, I'm trying to make a document calculating amount of time spent over a work-day, in different time periods. An example could be an over-all time period from 18.30 to 05.30. Cell A should define time spent between 18.00 and 21.00, Cell B define time spent between 21.00 and 00.00 and Cell C define time spent between 00.00 and 06.00. The result will be Cell A = 2,5 hours, Cell B = 3 hours and Cell C = 5,5 hours. But what formula should I use to get to the results? In advance thanks a lot! Best, Sune.

**JeteMc** · 03-21-2019, 08:01 PM

Hello SuneLolk and Welcome to Excel Forum.
The formula(s) used will depend on the setup. Supposing that 18:30 is in cell D3 and 5:30 is in cell E3 you could:
For A3:B3 use: =MIN($E3+($D3>$E3),A$2)-MAX($D3,A$1)
For C3 use: =E3+(D3>E3)-D3-SUM(A3:B3)
For future reference you will usually get faster if you upload a sample.
To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

Formula for defining time spent in specified time periods

LinkBack

Thread Tools

Rate This Thread

Display

Formula for defining time spent in specified time periods

Re: Formula for defining time spent in specified time periods

Thread Information

Users Browsing this Thread

Similar Threads

Total time spent over a term where monthly time reduces

[SOLVED] Stop Time Button to capture Time Spent

Excel not capturing the system user name, Start time, end time and total time spent

Calculate time elapsed, excluding time not spent working

Formula to Calculate Total time spent between Start Date & Time and End Date & Time

Defining core time periods in a day

Time Tracker - I need a macro that calculates the time spent on an activity

Bookmarks

Bookmarks

Posting Permissions