Convert Sum of Time to Decimals

[dwgnome]

In this spreadsheet start and stop times are clocked in [h:mm] format in specific columns. The row has nine such available start/stop iterations. Each row represents a complete 24-hr day. In most cases only a few Time columns will have data. The first Time column is presumed to always be a start time of midnight (12:00). I have this relatively simple formula that calculates the total cumulative operating time in hours and minutes and converted to decimal hours. It seems to work fine, except I cannot get the case where there is no stop times during the day and the answer should be 24.00 hours. That is the entire row is blank with the exception of the first time being 0:00. Since there is no 24:00 clock time, a 0:00 is input, representing 12:00:00 AM. This is fine; however, I cannot get 24 to show up in the Daily Total column.

Can someone show me how to reveal the 24 hours value? I would like to show 24:00 (probably only as text) and 24.00 as number. Columns with FT, Mode and Reason can be ignored.

Also, I would like to invoke a rule, that if the total is between 23 hours and 50 minutes and 23 hours and 59 minutes, to round up to 24 hours showing as 24.00 hours.

If there is a more elegant solution using perhaps SUMIF or SUMPRODUCT that can look for the word START and STOP and get the times in the cell just before and add/subtract as needed – I would welcome any suggestions as well.

Excel Workbook
	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	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
2	Day	1				2				3				4				5				6				7				8				9				Daily Total (Hrs:Mins)	Daily Total (Hrs)
3		Time	Mode	FT	Reason	Time	Mode	FT	Reason	Time	Mode	FT	Reason	Time	Mode	FT	Reason	Time	Mode	FT	Reason	Time	Mode	FT	Reason	Time	Mode	FT	Reason	Time	Mode	FT	Reason	Time	Mode	FT	Reason
4	1	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
5	CG	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
6	DB	0:00	Start	G	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	0:00	0.00
7	AX1	0:00	Run	G	*	4:05	Stop	G	*	5:05	Start	G	*	5:15	Stop	G	*	5:20	Start	G	*	5:25	Stop	G	0	7:10	Start	G	2	7:50	Stop	G	4	13:30	Start	G	2	15:30	15.50
8	Ax2	0:00	Run	G	*	5:00	Stop	G	*	5:00	Start	G	*	*	Stop	G	*	*	Start	G	*	*	Stop	G	0	*	Start	G	2	5:01	Stop	G	4	5:02	Start	G	2	23:59	23.98
9	KX	0:04	Run	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	23:56	23.93
Start Stop

 

[Sandeep Warrier]

Try formatting the time columns as Custom Format [hh]:mm
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[dwgnome]

I tried formating to [hh]:mm in the Daily Total column and the number turned to 576:00. When I divide by 24 it gives 24:00. Conversion to decimal does not work in the next column either. This solution however, that will not work for the rows that have data. Changing the formatting results in incorrect values.

Need a solution that applies in all cases.

Thank you.

 

[dwgnome]

Bump.

 

[albasheer]

Try this in AM6:

=IF(IF(AL6=24,24,HOUR((VALUE(AL6)))+ROUND(MINUTE((VALUE(AL6)))/60,4))>=23+50/60,24,IF(AL6=24,24,HOUR((VALUE(AL6)))+ROUND(MINUTE((VALUE(AL6)))/60,4)))

If you wish to round to 24 when total time is >=23:50 not just >23:50, then change the 50/60 in the formula above to 49.5/60. You have to do this because of the round function.

 

[dwgnome]

albasheer,

That worked perfectly.

Thank you so very much.