TIMEVALUE()

[chachu]

My problem is related to the above function.

I have 2 sets of times... on start and one end.... basically in columns A6 and B6 respectively. in C6 I have to calculate the difference in times... which is usually under 5 hours. And that works fine... but this is done on a a daily basis, at the end of the week i have to calculate all the differences in time.
My problem arrises when TIMEVALUE() requires a text datafield, and the answer of the difference is a custom. Therefore I cannot calculate the weekly total because it is in custom and not text.

Kinda new to excel so if u please bear with me.
Thank you.

 

[lenze]

I'm not exactly sure what you need, but I believe this is close without using the Timevalue Function

Book1
	A	B	C	D
1	DAY	START	END	HOURS
2	Mon	9:00 AM	1:00 PM	4
3	Tue	8:30 AM	12:30 PM	4
4	Wed	6:00 AM	11:00 AM	5
5	Thu	9:45 AM	3:00 PM	5.25
6	Fri	7:30 AM	1:00 PM	5.5
7
8			TOTAL	23.75
...

Note that Column D is formatted General or Number

 

[chachu]

i understand your equation.. but the total values must be in a time format...

so instead of 4.5 it should be 4:30.

thanks

 

[chachu]

also the hours must be in time format too

 

[lenze]

Have a look at this page by Chip Pearson

http://www.cpearson.com/excel/datetime.htm#AddingTimes

 

[lenze]

Here's another example. Note that the total cell is formatted with custom formatting

[h]:mm

Book1
	A	B	C	D
1	DAY	START	END	HRS
2	Mon	9:00 AM	12:30 PM	3:30
3	Tue	8:00 AM	3:00 PM	7:00
4	Wed	7:30 AM	11:00 AM	3:30
5	Thu	6:00 AM	1:30 PM	7:30
6	Fri	8:30 AM	11:30 AM	3:00
7
8			TOTAL	24:30
...

HTH