Help Return Value Based on Time in Two Different Cells

**LOK25** · 02-28-2020, 10:54 AM

Hello and thanks,

I believe there should be a simple solution to my Excel question.

Say Cell A3 has a value of 5:00 AM and B3 has a value of 9:00 AM.

Assume a work shift starts at 7:00 AM and ends at 4:30 PM, I am trying to do the following:

Return the difference in hrs between A3 and B3 if A3 through B3 values fall between 700 am to 430 pm.so in this case the answer is 2 hrs (from 7 am to 9am occured within normal shift) and if A3:B3 time didnt occur between 7 to 430 retirn a value of 0.

Can anyone assist? Thanks

**SimonLock** · 02-28-2020, 11:45 AM

If you put the normal start time in E1, and the normal finish time in E2, you can use the following:

=IF(OR(MAX(A3:B3)<E1,MIN(A3:B3)>E2),0,MIN(B3,E2)-MAX(A3,E1))

This returns a 0 if the values fall outside normal times, otherwise it works out the time worked during "normal hours"

**mehmetcik** · 02-28-2020, 11:56 AM

C3 =HOUR(B3-A3)-D3
D3 =MAX(HOUR(B3)-16.5,0)+MAX(7-HOUR(A3),0)

Format both cells as general.

**LOK25** · 02-28-2020, 06:54 PM

I couldn't get any of the suggested equations to work. Could this help?

Work Schedule:

Start Of Work Shift: Cell B3: 7:00 AM
End of Work Shift: Cell C3: 4:30 PM

Header: Depart Time Header 2: Arrival Time
5:00 AM 9:00 AM

So is there any equation to have Excel calculate the difference between Arrival and Depart Times that were in between the work shift? In this case, the 2 hours from 7:00 to 9:00 am?

Thanks

**mehmetcik** · 02-28-2020, 07:09 PM

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Help Return Value Based on Time in Two Different Cells

LinkBack

Thread Tools

Rate This Thread

Display

Help Return Value Based on Time in Two Different Cells

Re: Help Return Value Based on Time in Two Different Cells

Re: Help Return Value Based on Time in Two Different Cells

Re: Help Return Value Based on Time in Two Different Cells

Re: Help Return Value Based on Time in Two Different Cells

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Return a different number based on range that a time falls into

Value at end of time series based on Rate of Return???

Return the Earliest time in a range based on a min value

[SOLVED] return value based on age at a given point in time

Return value based on closest time/date and name

[SOLVED] Return indicated value based on time elapsed since specified date

Return a value based on a time range

Bookmarks

Bookmarks

Posting Permissions