Spread hour by colums

[vogel997]

Day Descr2 Name Start time End time 6:00 - 7:00AM 7:00 - 8:00AM 8:00 - 9:00AM 9:00 - 10:00AM 10:00 - 11:00AM 11:00 - 12:00PM 12:00 - 1:00PM 1:00 - 2:00PM 2:00 - 3:00PM 3:00 - 4:00PM 4:00 - 5:00PM 5:00 - 6:00PM 6:00 - 7:00PM 7:00 - 8:00PM 8:00 - 9:00PM 9:00 - 10:00PM 10:00 - 11:00PM 11:00 - 12:00AM 12:00 - 1:00AM 1:00 - 2:00AM 2:00 - 3:00AM Mon Fulltime John Johnes 18:15 01:30 - - - - - - - - - - - - 0.75 1.00 1.00 1.00 1.00 1.00 1.00 0.50 - Tue Fulltime John Johnes 10:00 20:00 - - - - 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 - - - - - - - Wed Fulltime John Johnes 08:00 16:00 - - 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 - - - - - - - - - - - Thu Fulltime John Johnes 17:30 02:30 - - - - - - - - - - - 0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 0.50 Fri Fulltime John Johnes 14:00 22:30 - - - - - - - - 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 0.50 - - - - Sat Fulltime John Johnes 16:00 00:30 - - - - - - - - - - 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 0.50 - - Sun Fulltime John Johnes 12:15 18:45 - - - - - - 0.75 1.00 1.00 1.00 1.00 1.00 0.75 - - - - - - - - <tbody> </tbody>

<tbody>
</tbody>

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
1	6:00 AM	6:00 AM - 7:00 AM	7:00 AM - 8:00 AM

<tbody>
</tbody>

Sheet1​

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

Worksheet Formulas Cell Formula C1 =TEXT(B1,"h:mm AM/PM")&" - "&TEXT(B1+1/24,"h:mm AM/PM") D1 =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") <tbody> </tbody>

<tbody>
</tbody>

 

[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

				04:00 AM	06:00 AM	07:00 AM	08:00 AM	09:00 AM	10:00 AM	11:00 AM	12:00 PM	01:00 PM	02:00 PM	03:00 PM	04:00 PM	05:00 PM	06:00 PM	07:00 PM	08:00 PM	09:00 PM	10:00 PM	11:00 PM	12:00 AM	01:00 AM	02:00 AM
Day	Descr2	Name	Start time	End time	07:00 AM	08:00 AM	09:00 AM	10:00 AM	11:00 AM	12:00 PM	01:00 PM	02:00 PM	03:00 PM	04:00 PM	05:00 PM	06:00 PM	07:00 PM	08:00 PM	09:00 PM	10:00 PM	11:00 PM	12:00 AM	01:00 AM	02:00 AM	03:00 AM
Mon	Fulltime	John Johnes	06:55	14:20	0.08	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))))
Tue	Fulltime	John Johnes	06:55	14:20	0.08	1.00	1.00	1.00	1.00	1.00	1.00	1.00	0.33	-	-	-	-	-	-	-	-	-	-	-	-
Wed	Fulltime	John Johnes	10:00	01:30	-	-	-	-	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	0.50	-
Tue	Fulltime	John Johnes	16:30	01:30	-	-	-	-	-	-	-	-	-	-	0.50	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	0.50	-
Wed	Fulltime	John Johnes	12:10	00:15	-	-	-	-	-	-	0.83	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	0.25	-	-
Thu	Fulltime	John Johnes	16:30	01:30	-	-	-	-	-	-	-	-	-	-	0.50	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	0.50	-

<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> </tbody>

 

[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
1			6:00	7:00	8:00	9:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00	0:00	1:00	2:00	3:00	4:00	5:00
2	18:15	1:15													0:45	1:00	1:00	1:00	1:00	1:00	1:00	1:00	0:15

<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>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D1 =C1+1/24 E1 =D1+1/24 F1 =E1+1/24 G1 =F1+1/24 H1 =G1+1/24 I1 =H1+1/24 J1 =I1+1/24 K1 =J1+1/24 L1 =K1+1/24 M1 =L1+1/24 N1 =M1+1/24 O1 =N1+1/24 P1 =O1+1/24 Q1 =P1+1/24 R1 =Q1+1/24 S1 =R1+1/24 T1 =S1+1/24 U1 =T1+1/24 V1 =U1+1/24 W1 =V1+1/24 X1 =W1+1/24 Y1 =X1+1/24 Z1 =Y1+1/24 C2 =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))) D2 =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))) E2 =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))) F2 =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))) G2 =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))) H2 =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))) I2 =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))) J2 =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))) K2 =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))) L2 =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))) M2 =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))) N2 =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))) O2 =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))) P2 =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))) Q2 =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))) R2 =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))) S2 =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))) T2 =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))) U2 =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))) V2 =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))) W2 =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))) X2 =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))) Y2 =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))) Z2 =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))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[vogel997]

Thank you for your help.