Calculate total hours that fall between a specific time period (Over midnight)

[nik_re]

Hi All, I am new in this wonderful forum.

Hope my question will be clear.

Need to know the specific time that fall between 22.00 and 6.00 a.m. when the time in (of an operator) and the time out can be at any time.

Basically, in my sheet the formula in cell D6 need to be fixed.

Some cases are ok with what I did but this last donīt work.

Please help me wonderful people

[etaf]

can you post a sample file , see how yellow banner

[nik_re]

can you post a sample file , see how yellow banner

Sorry... first time.. .I did not upload it. Now it should be attached.

Thank you for your patience

[ThxAlot]

Your formulae in the mockup is all Greek to me

Let's cut it through. As Excel deals with time as decimal ranging from 0.0 (00:00:00) to 0.999988425925926 (23:59:59), arithmetic subtraction applies. As to overnight, plus 1 to the ending time is enought, eg A1=23:15:00, A2=6:20:00

Please Login or Register  to view this content.

Generally speaking, Excel is intelligent enough to convert the result to "Time" format. You may use such a formula just in case,

Please Login or Register  to view this content.

[nik_re]

Hi,

I have tried to do it better, there are two types of formula that I am using as you can see in the document.
The second one work for everything except where the cell is highlighted.

ThxAlot, I am not understanding in which cell I should write the formula that you said.

Thank you for all these replies.

Trying to do better and better..

[nik_re]

Your formulae in the mockup is all Greek to me

Let's cut it through. As Excel deals with time as decimal ranging from 0.0 (00:00:00) to 0.999988425925926 (23:59:59), arithmetic subtraction applies. As to overnight, plus 1 to the ending time is enought, eg A1=23:15:00, A2=6:20:00

Please Login or Register  to view this content.

Generally speaking, Excel is intelligent enough to convert the result to "Time" format. You may use such a formula just in case,

Please Login or Register  to view this content.

the problem is taht if the operator 1 will access the office at 21 and left at 5, then when the formula will do the check with the upper(06) and lower(22) limit, will not understand the time that fall between 22 and 6. I donīt know how to fix it, while it works if lower and upper are sorted (0... 23)

[Toonies]

Hi what are the actual shift times that staff work

i.e. 22:00 - 6:00
06:00 - 14:00
14:00 - 22:00

If you base your upper and lower times on a 48hr clock, so 6:00am the next day would be entered as 30:00 etc and format the cells as h:mm am/pm it would be displayed as 6:00am and in the formula bar as 01/01/1900 06:00:00 or you can display it as 30:00 formatting it to [h]:mm

use the following formula

Please Login or Register  to view this content.

this is one of Daddylonglegs formulas

[nik_re]

Hi Toniees,

thank you. I am using the date and time to fix the issue (basically is what you suggested)

this is just the beginning of a project.

the time shift is 9.00 to 17.00

I will have to distinguish the hours that operator will work in weekdays and weekend too.

I am going to open a new thread, in the meanwhile thank you for your answer. Appreciated.

[Toonies]

you can also check this thread out and have a look at BlindAlley post and attachment called "Shift Hours"

https://www.excelforum.com/excel-for...e-formula.html


https://www.excelforum.com/attachmen...ift-hours.xlsx