Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time calculations in VBA
hai guys, i am also here with the similar problem. here is the situation. there are 4 list's for start time & end time A B C D E 1 st time | end time| hours 2 HH MM | HH MM | 3 ------------------------- 4 5 00 6 30 1:30 ( what i am doing here to get the value for this cel is E4=TEXT(TIME(a4,b4,0)-TIME(C4,D4,0),"hh:mm") and this cell is in time format like "13:30" Now i don't want this to be like 1.30, instead i want like in 1.50Hrs for calculation based on hours comsumed). And, i am using this E4 cell in VBA. there are similar rows like 4. and i want to add the Hours(E) column i.e if E4 =1.50, E5=2.75 then i need to get 4.25. how to do this in VBA. plz help me Thanks & regards, Mahes -- itsmahesh ----------------------------------------------------------------------- itsmaheshp's Profile: http://www.excelforum.com/member.php...fo&userid=1585 View this thread: http://www.excelforum.com/showthread.php?threadid=27689 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time calculations in VBA
Mahesh, what you are currently doing is that you are converting the result to a string by using TEXT. Now coming to your main problem of depicting 1:30 as 1.5. Date/Time is represented in excel using numbers and 1 day is equivalent to 1 unit. As you see the base is DAY. Now sinve you want the base as HOUR, you can simply multiply your result by 24. This will give you the required 1.5 instead of 1:30. Also remember to format this as a number and not date/time. -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=276891 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time calculations for Scheduled vs Actual Employee Time and Wages | Excel Discussion (Misc queries) | |||
Time calculations for Scheduled Time vs. Actual Time Worked | Excel Discussion (Misc queries) | |||
Time calculations for Scheduled vs Actual Employee Time and Wages | Excel Discussion (Misc queries) | |||
Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions |