Spread hour by colums

[vogel997]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 1128"]
<tbody>[TR]
[TD="width: 34, bgcolor: #375623"]Day

[/TD]
[TD="width: 70, bgcolor: #375623"]Descr2
[/TD]
[TD="width: 104, bgcolor: #375623"]Name
[/TD]
[TD="width: 70, bgcolor: #375623"]Start time
[/TD]
[TD="width: 65, bgcolor: #375623"]End time
[/TD]
[TD="width: 53, bgcolor: #375623"]6:00 - 7:00AM
[/TD]
[TD="width: 56, bgcolor: #375623"]7:00 - 8:00AM
[/TD]
[TD="width: 53, bgcolor: #375623"]8:00 - 9:00AM
[/TD]
[TD="width: 61, bgcolor: #375623"]9:00 - 10:00AM
[/TD]
[TD="width: 61, bgcolor: #375623"]10:00 - 11:00AM
[/TD]
[TD="width: 60, bgcolor: #375623"]11:00 - 12:00PM
[/TD]
[TD="width: 53, bgcolor: #375623"]12:00 - 1:00PM
[/TD]
[TD="width: 53, bgcolor: #375623"]1:00 - 2:00PM
[/TD]
[TD="width: 53, bgcolor: #375623"]2:00 - 3:00PM
[/TD]
[TD="width: 53, bgcolor: #375623"]3:00 - 4:00PM
[/TD]
[TD="width: 64, bgcolor: #375623"]4:00 - 5:00PM
[/TD]
[TD="width: 53, bgcolor: #375623"]5:00 - 6:00PM
[/TD]
[TD="width: 53, bgcolor: #375623"]6:00 - 7:00PM
[/TD]
[TD="width: 53, bgcolor: #375623"]7:00 - 8:00PM
[/TD]
[TD="width: 53, bgcolor: #375623"]8:00 - 9:00PM
[/TD]
[TD="width: 60, bgcolor: #375623"]9:00 - 10:00PM
[/TD]
[TD="width: 60, bgcolor: #375623"]10:00 - 11:00PM
[/TD]
[TD="width: 61, bgcolor: #375623"]11:00 - 12:00AM
[/TD]
[TD="width: 53, bgcolor: #375623"]12:00 - 1:00AM
[/TD]
[TD="width: 53, bgcolor: #375623"]1:00 - 2:00AM
[/TD]
[TD="width: 53, bgcolor: #375623"]2:00 - 3:00AM
[/TD]
[/TR]
[TR]
[TD]Mon
[/TD]
[TD]Fulltime
[/TD]
[TD]John Johnes
[/TD]
[TD="align: right"]18:15
[/TD]
[TD="align: right"]01:30
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] 0.75
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 0.50
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[/TR]
[TR]
[TD]Tue
[/TD]
[TD]Fulltime
[/TD]
[TD]John Johnes
[/TD]
[TD="align: right"]10:00
[/TD]
[TD="align: right"]20:00
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[/TR]
[TR]
[TD]Wed
[/TD]
[TD]Fulltime
[/TD]
[TD]John Johnes
[/TD]
[TD="align: right"]08:00
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[/TR]
[TR]
[TD]Thu
[/TD]
[TD]Fulltime
[/TD]
[TD]John Johnes
[/TD]
[TD="align: right"]17:30
[/TD]
[TD="align: right"]02:30
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] 0.50
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 0.50
[/TD]
[/TR]
[TR]
[TD]Fri
[/TD]
[TD]Fulltime
[/TD]
[TD]John Johnes
[/TD]
[TD="align: right"]14:00
[/TD]
[TD="align: right"]22:30
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 0.50
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[/TR]
[TR]
[TD]Sat
[/TD]
[TD]Fulltime
[/TD]
[TD]John Johnes
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="align: right"]00:30
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 0.50
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[/TR]
[TR]
[TD]Sun
[/TD]
[TD]Fulltime
[/TD]
[TD]John Johnes
[/TD]
[TD="align: right"]12:15
[/TD]
[TD="align: right"]18:45
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] 0.75
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 1.00
[/TD]
[TD="bgcolor: transparent"] 0.75
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] -
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'm looking for a formula that spreads the hours by column that goes past midnight.
Thank you for any help.

 

[Aryatect]

Hi,

You can use the below, I have given the start time in a distinct cell(B1), if you want you can hard-code that in C1:

	B	C	D
6:00 AM - 7:00 AM	7:00 AM - 8:00 AM

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]6:00 AM[/TD]

</tbody>

Sheet1​

Copy D1 formula to other columns E1, F1, G1 etc...

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]=TEXT(B1,"h:mm AM/PM")&" - "&TEXT(B1+1/24,"h:mm AM/PM")[/TD]
[/TR]
[TR]
[TH]D1[/TH]
[TD="align: left"]=TEXT(RIGHT(C1,LEN(C1)-SEARCH("- ",C1)-1)*1,"h:mm AM/PM")&" - "&TEXT(RIGHT(C1,LEN(C1)-SEARCH("- ",C1)-1)*1+1/24,"h:mm AM/PM")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[vogel997]

Hi,
I'm looking for the result to show either 1,for full hour, or partial hour according to the start an end time, to show in the appropriate column.
Thank you

 

[sandy666]

maybe show example of expected result

 

[vogel997]

The end result is in the table above, that was manually entered in each column. I'm looking for a formula in each field that populates the entries ("1.00" or partial hour) according to the start ans end time in the fourth and fifth column.

Thank you

 

[vogel997]

I was able to solve the problem, please let me know if the any shorter or better solution

[TABLE="width: 1128"]
<colgroup><col width="34" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1160;"> <col width="70" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2372;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3549;"> <col width="70" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2372;"> <col width="65" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2201;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1809;"> <col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 1894;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1809;"> <col width="61" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2065;" span="2"> <col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="53" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1792;" span="4"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2184;"> <col width="53" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1792;" span="4"> <col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2048;" span="2"> <col width="61" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2065;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1809;" span="3"> <tbody>[TR]
[TD="width: 34, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] "] [/TD]
[TD="width: 70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] "] [/TD]
[TD="width: 104, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] "] [/TD]
[TD="width: 70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] "] [/TD]
[TD="width: 65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]04:00 AM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]06:00 AM[/TD]
[TD="width: 56, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]07:00 AM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]08:00 AM[/TD]
[TD="width: 61, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]09:00 AM[/TD]
[TD="width: 61, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]10:00 AM[/TD]
[TD="width: 60, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]11:00 AM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]12:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]01:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]02:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]03:00 PM[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]04:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]05:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]06:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]07:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]08:00 PM[/TD]
[TD="width: 60, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]09:00 PM[/TD]
[TD="width: 60, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]10:00 PM[/TD]
[TD="width: 61, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]11:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]12:00 AM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]01:00 AM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]02:00 AM[/TD]
[/TR]
[TR]
[TD="width: 34, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] "]Day[/TD]
[TD="width: 70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] "]Descr2[/TD]
[TD="width: 104, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] "]Name[/TD]
[TD="width: 70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] "]Start time[/TD]
[TD="width: 65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] "]End time[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]07:00 AM[/TD]
[TD="width: 56, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]08:00 AM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]09:00 AM[/TD]
[TD="width: 61, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]10:00 AM[/TD]
[TD="width: 61, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]11:00 AM[/TD]
[TD="width: 60, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]12:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]01:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]02:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]03:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]04:00 PM[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]05:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]06:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]07:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]08:00 PM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]09:00 PM[/TD]
[TD="width: 60, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]10:00 PM[/TD]
[TD="width: 60, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]11:00 PM[/TD]
[TD="width: 61, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]12:00 AM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]01:00 AM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]02:00 AM[/TD]
[TD="width: 53, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=375623]#375623[/URL] , align: right"]03:00 AM[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]Mon[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]Fulltime[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]John Johnes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]06:55[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]14:20[/TD]
[TD="bgcolor: transparent"] 0.08 [/TD]
[TD="bgcolor: transparent, colspan: 20"] IF($E3<$D3,IF(AND(F$1<$D3,F$2>$D3),MOD(F$2-$D3,1)*24,IF(AND(F$1>=$D3,F$2<=$E3+(1)),1,IF(AND(F$1<$E3+1,F$2>$E3+1),MOD($E3-F$1,1)*24,0))),IF(AND(F$1<$D3,F$2>$D3),MOD(F$2-$D3,1)*24,IF(AND(F$1>=$D3,F$2<=$E3),1,IF(AND(F$1<$E3,F$2>$E3),MOD($E3-F$1,1)*24,0)))) [/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]Tue[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]Fulltime[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]John Johnes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]06:55[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]14:20[/TD]
[TD="bgcolor: transparent"] 0.08 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 0.33 [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]Wed[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]Fulltime[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]John Johnes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]10:00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]01:30[/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 0.50 [/TD]
[TD="bgcolor: transparent"] - [/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]Tue[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]Fulltime[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]John Johnes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]16:30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]01:30[/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] 0.50 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 0.50 [/TD]
[TD="bgcolor: transparent"] - [/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]Wed[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]Fulltime[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]John Johnes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]12:10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]00:15[/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] 0.83 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 0.25 [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]Thu[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]Fulltime[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] "]John Johnes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]16:30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]01:30[/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] -
[/TD]
[TD="bgcolor: transparent"] 0.50 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 1.00 [/TD]
[TD="bgcolor: transparent"] 0.50 [/TD]
[TD="bgcolor: transparent"] - [/TD]
[/TR]
</tbody>[/TABLE]

 

[Aryatect]

Hi,

I too came up with a solution just now, but I think it is similar length to your formula if not more lengthy , will try if can do better:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]7:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]21:00[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]23:00[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]2:00[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]4:00[/TD]
[TD="align: right"]5:00[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]18:15[/TD]
[TD="align: right"]1:15[/TD]

[TD="align: right"]0:45[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]0:15[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=C1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=D1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F1[/TH]
[TD="align: left"]=E1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1[/TH]
[TD="align: left"]=F1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1[/TH]
[TD="align: left"]=G1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I1[/TH]
[TD="align: left"]=H1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J1[/TH]
[TD="align: left"]=I1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K1[/TH]
[TD="align: left"]=J1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L1[/TH]
[TD="align: left"]=K1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M1[/TH]
[TD="align: left"]=L1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N1[/TH]
[TD="align: left"]=M1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O1[/TH]
[TD="align: left"]=N1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P1[/TH]
[TD="align: left"]=O1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q1[/TH]
[TD="align: left"]=P1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]R1[/TH]
[TD="align: left"]=Q1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S1[/TH]
[TD="align: left"]=R1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]T1[/TH]
[TD="align: left"]=S1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U1[/TH]
[TD="align: left"]=T1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]V1[/TH]
[TD="align: left"]=U1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]W1[/TH]
[TD="align: left"]=V1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]X1[/TH]
[TD="align: left"]=W1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Y1[/TH]
[TD="align: left"]=X1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Z1[/TH]
[TD="align: left"]=Y1+1/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(OR(C$1<$A2-1/24,C$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(B$1<$A2-1/24,B$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",C$1+1/24-$A2,IF(IF(OR(D$1<$A2-1/24,D$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(C$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(OR(D$1<$A2-1/24,D$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(C$1<$A2-1/24,C$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",D$1+1/24-$A2,IF(IF(OR(E$1<$A2-1/24,E$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(D$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF(OR(E$1<$A2-1/24,E$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(D$1<$A2-1/24,D$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",E$1+1/24-$A2,IF(IF(OR(F$1<$A2-1/24,F$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(E$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(OR(F$1<$A2-1/24,F$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(E$1<$A2-1/24,E$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",F$1+1/24-$A2,IF(IF(OR(G$1<$A2-1/24,G$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(F$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(OR(G$1<$A2-1/24,G$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(F$1<$A2-1/24,F$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",G$1+1/24-$A2,IF(IF(OR(H$1<$A2-1/24,H$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(G$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IF(OR(H$1<$A2-1/24,H$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(G$1<$A2-1/24,G$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",H$1+1/24-$A2,IF(IF(OR(I$1<$A2-1/24,I$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(H$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=IF(OR(I$1<$A2-1/24,I$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(H$1<$A2-1/24,H$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",I$1+1/24-$A2,IF(IF(OR(J$1<$A2-1/24,J$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(I$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=IF(OR(J$1<$A2-1/24,J$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(I$1<$A2-1/24,I$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",J$1+1/24-$A2,IF(IF(OR(K$1<$A2-1/24,K$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(J$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=IF(OR(K$1<$A2-1/24,K$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(J$1<$A2-1/24,J$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",K$1+1/24-$A2,IF(IF(OR(L$1<$A2-1/24,L$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(K$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]=IF(OR(L$1<$A2-1/24,L$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(K$1<$A2-1/24,K$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",L$1+1/24-$A2,IF(IF(OR(M$1<$A2-1/24,M$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(L$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]=IF(OR(M$1<$A2-1/24,M$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(L$1<$A2-1/24,L$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",M$1+1/24-$A2,IF(IF(OR(N$1<$A2-1/24,N$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(M$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N2[/TH]
[TD="align: left"]=IF(OR(N$1<$A2-1/24,N$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(M$1<$A2-1/24,M$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",N$1+1/24-$A2,IF(IF(OR(O$1<$A2-1/24,O$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(N$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O2[/TH]
[TD="align: left"]=IF(OR(O$1<$A2-1/24,O$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(N$1<$A2-1/24,N$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",O$1+1/24-$A2,IF(IF(OR(P$1<$A2-1/24,P$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(O$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P2[/TH]
[TD="align: left"]=IF(OR(P$1<$A2-1/24,P$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(O$1<$A2-1/24,O$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",P$1+1/24-$A2,IF(IF(OR(Q$1<$A2-1/24,Q$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(P$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q2[/TH]
[TD="align: left"]=IF(OR(Q$1<$A2-1/24,Q$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(P$1<$A2-1/24,P$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",Q$1+1/24-$A2,IF(IF(OR(R$1<$A2-1/24,R$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(Q$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]R2[/TH]
[TD="align: left"]=IF(OR(R$1<$A2-1/24,R$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(Q$1<$A2-1/24,Q$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",R$1+1/24-$A2,IF(IF(OR(S$1<$A2-1/24,S$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(R$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S2[/TH]
[TD="align: left"]=IF(OR(S$1<$A2-1/24,S$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(R$1<$A2-1/24,R$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",S$1+1/24-$A2,IF(IF(OR(T$1<$A2-1/24,T$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(S$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]T2[/TH]
[TD="align: left"]=IF(OR(T$1<$A2-1/24,T$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(S$1<$A2-1/24,S$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",T$1+1/24-$A2,IF(IF(OR(U$1<$A2-1/24,U$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(T$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U2[/TH]
[TD="align: left"]=IF(OR(U$1<$A2-1/24,U$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(T$1<$A2-1/24,T$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",U$1+1/24-$A2,IF(IF(OR(V$1<$A2-1/24,V$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(U$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]V2[/TH]
[TD="align: left"]=IF(OR(V$1<$A2-1/24,V$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(U$1<$A2-1/24,U$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",V$1+1/24-$A2,IF(IF(OR(W$1<$A2-1/24,W$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(V$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]W2[/TH]
[TD="align: left"]=IF(OR(W$1<$A2-1/24,W$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(V$1<$A2-1/24,V$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",W$1+1/24-$A2,IF(IF(OR(X$1<$A2-1/24,X$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(W$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]X2[/TH]
[TD="align: left"]=IF(OR(X$1<$A2-1/24,X$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(W$1<$A2-1/24,W$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",X$1+1/24-$A2,IF(IF(OR(Y$1<$A2-1/24,Y$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(X$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Y2[/TH]
[TD="align: left"]=IF(OR(Y$1<$A2-1/24,Y$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(X$1<$A2-1/24,X$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",Y$1+1/24-$A2,IF(IF(OR(Z$1<$A2-1/24,Z$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(Y$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Z2[/TH]
[TD="align: left"]=IF(OR(Z$1<$A2-1/24,Z$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",IF(IF(OR(Y$1<$A2-1/24,Y$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",Z$1+1/24-$A2,IF(IF(OR(AA$1<$A2-1/24,AA$1>IF($B2<$A2,$B2+1,$B2)+1/24),"",FALSE)="",1/24-(Z$1-IF($B2<$A2,$B2+1,$B2)),1/24)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[vogel997]

Thank you for your help.