Calculating day shift and night shift hours from date/time

[BenR]

I want to find the hours worked in day shift and night shift. Day shift is from 06:30 to 18:30 and night shift is from 18:30 to 06:30. Listed below is an example of my date/times.
Note that the night shift carries over to the next day.

Start/Finish
21/12/09 07:00 to 21/12/09 11:09
21/12/09 07:46 to 21/12/09 14:41
21/12/09 12:13 to 21/12/09 22:08
21/12/09 16:40 to 21/12/09 18:05
21/12/09 19:40 to 22/12/09 02:34
21/12/09 23:20 to 22/12/09 04:39
22/12/09 02:06 to 22/12/09 06:15

any ideas?

 

[GerryZ]

i COPY YOUR FORMULA BU DOES' WORK!

 

[Michael Avidan]

i COPY YOUR FORMULA BU DOES' WORK!

To Whom are you replying to ?

If to me - please attach your workbook with my "non working" suggested formula.

My Doctor never agreed to prescribe Antibiotics, and/or any other medicine, whitout examining my throat first...

Michael Avidan
MS Excel - MVP

 

[GerryZ]

Hi Michael
Tank you for the answer
First of all sorry for misunderstding.. is my first day on this excl messge bord
i'm using Excel 2013 italian version
and your formula works very well but there only on problem when i digit 7:00 /13:00 it gives m ##########
probably one baco unbelivelable
I wish to send to you the workbook But I don't know how to do it
thank you Gerardo

 

[GerryZ]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael Avidan]

Hi Michael
Tank you for the answer
First of all sorry for misunderstding.. is my first day on this excl messge bord
i'm using Excel 2013 italian version
and your formula works very well but there only on problem when i digit 7:00 /13:00 it gives m ##########
probably one baco unbelivelable
I wish to send to you the workbook But I don't know how to do it
thank you Gerardo

Hi, Gerardo

ou can try to attach it to yor nextpotHRE andor sen itto me via E-Miail.

My addess is: micky-a<at>tapuz.co.il

I'm sure you'll know what to type instead the: <at>

*** DO NOT FORGT TO PUT THE FORMULA IN PLACE ***

PLEASE TYPE YOUR NAM IN THE E-MAIL SUBJECT'S FILED.

Michael

 

[Michael Avidan]

http://jpg.co.il/download/53b72f3be91b9.png[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Gerardo,

I just noticed your second editted post with the formula.

So before you mail me the file - please check out the formula presented in the above new link

Michael Avidan
MS Excel MVP

 

[GerryZ]

Hello Michael
you gave me wrong e.mal address
waiting or the right one
Bye

 

[GerryZ]

Michael
wowwwww!! now the formula work perfectly
I haven't undersood what you have don but now is ok
Any way what was the problem ? was Excel 2013? Excel italian verion?
thak ou very much without your help I could not it
Ciao Mike

 

[katish]

Michael - as you can see a 4 year old answer is not too late to be of help

I signed up to forum just to thank your reply as It helped my issue as well. I am trying to understand the logic in your formula, if you could explain the purpose of each bit in english.. I know what each of the functions return, but I can't wrap my head around how the whole thing works together.

And lastly, your edition seems to correct the issue with returning a negative elapsed time, but again, I don't get what it does.

I ask because I am using cell references instead of hard coded numbers for the night time delimiters, and I dont know which cell reference to use in place of the 6 in the last argument.

Sorry my english, let me know if I don't make sense somewhere in there!

Katia

http://jpg.co.il/download/53b72f3be91b9.png[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Gerardo,

I just noticed your second editted post with the formula.

So before you mail me the file - please check out the formula presented in the above new link

Michael Avidan
MS Excel MVP

 

[Michael Avidan]

http://jpg.co.il/download/53bbac4b99bb7.png

Katia,

1) My english is much worse than your therefore it will be difficult for me to explain the "complicated formula.

2) In the above link you will find the same formula reffering to worksheet cells

Michael Avidan
MS Excel MVP (2009-2015)
ISRAEL