TIMEVALUE()

chachu

New Member
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.

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I'm not exactly sure what you need, but I believe this is close without using the Timevalue Function
Book1
ABCD
1DAYSTARTENDHOURS
2Mon9:00 AM1:00 PM4
3Tue8:30 AM12:30 PM4
4Wed6:00 AM11:00 AM5
5Thu9:45 AM3:00 PM5.25
6Fri7:30 AM1:00 PM5.5
7
8TOTAL23.75
...

Note that Column D is formatted General or Number

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

also the hours must be in time format too

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

[h]:mm
Book1
ABCD
1DAYSTARTENDHRS
2Mon9:00 AM12:30 PM3:30
3Tue8:00 AM3:00 PM7:00
4Wed7:30 AM11:00 AM3:30
5Thu6:00 AM1:30 PM7:30
6Fri8:30 AM11:30 AM3:00
7
8TOTAL24:30
...

HTH

Replies
1
Views
104
Replies
4
Views
152
Replies
6
Views
209
Replies
4
Views
310
Replies
2
Views
277

Threads
1,219,580
Messages
6,149,114
Members
450,861
Latest member
metcala

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back