Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can we convert "2 days 16 hrs" to " 64hrs" using excel functions
i tried diffeernt methods. i can either convert days to hrs or hrs to days.
but am not able to do both. so if anybody can do it. plz let me know. thanks chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can we convert "2 days 16 hrs" to " 64hrs" using excel functions
If the data is how you stated, this will work
=LEFT(A1,FIND(" ",A1)-1)*24+MID(SUBSTITUTE(A1," hrs",""),FIND(" ",A1,FIND(" ",A1)+1)+1,99)&"hrs" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "chris" wrote in message ... i tried diffeernt methods. i can either convert days to hrs or hrs to days. but am not able to do both. so if anybody can do it. plz let me know. thanks chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can we convert "2 days 16 hrs" to " 64hrs" using excel functions
slightly better
=LEFT(A1,FIND(" ",A1)-1)*24+MID(TRIM(SUBSTITUTE(A1,"hrs","")),FIND(" ",A1,FIND(" ",A1)+1)+1,99)&"hrs" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "chris" wrote in message ... i tried diffeernt methods. i can either convert days to hrs or hrs to days. but am not able to do both. so if anybody can do it. plz let me know. thanks chris |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can we convert "2 days 16 hrs" to " 64hrs" using excel functions
Hi!
Maybe something like this: =IF(ISNUMBER(SEARCH(" day",A1)),24*LEFT(A1,FIND(" ",A1)-1),0)+IF(ISNUMBER(SEARCH(" hr",A1)),TRIM(RIGHT(SUBSTITUTE(A1," hrs",""),2)),0) It'll work on the following formats: 2 days 99 hrs 0 days 0 hrs 2 days 99 hrs (any number of days) days ( up to any 2 digits) hrs Biff "chris" wrote in message ... i tried diffeernt methods. i can either convert days to hrs or hrs to days. but am not able to do both. so if anybody can do it. plz let me know. thanks chris |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can we convert "2 days 16 hrs" to " 64hrs" using excel functions
I'm not sure if this is a possibility for you, but if you used two cells -- one
for hours and one for days, then your life may get easier. chris wrote: i tried diffeernt methods. i can either convert days to hrs or hrs to days. but am not able to do both. so if anybody can do it. plz let me know. thanks chris -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can we convert "2 days 16 hrs" to " 64hrs" using excel functions
If you have an actual Excel time in your cell, the format [h] will display the
64 hours for you. The brackets tell excel to display more than 24 hours. -- Regards, Fred "chris" wrote in message ... i tried diffeernt methods. i can either convert days to hrs or hrs to days. but am not able to do both. so if anybody can do it. plz let me know. thanks chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Desperately trying to build a paid time off accrual worksheet... | Excel Worksheet Functions | |||
Convert Excel 4 formula to Excel 2003 format | Excel Worksheet Functions | |||
Convert excel 2003 (in xml format) to excel 2000 | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
how to update functions in excel sheet? | Excel Worksheet Functions |