| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Time
The data I have is imported into Excel as:
A1 A2 LOG_OFF 10/13/2009 09:50:45 AM LOG_ON 10/13/2009 10:00:18 AM CO_Incoming 10/13/2009 10:34:15 AM CO_Incoming 10/13/2009 10:15:39 AM LOG_OFF 10/13/2009 10:15:54 AM LOG_ON 10/13/2009 10:27:13 AM WRAP_ON 10/13/2009 10:34:45 AM WRAP_OFF 10/13/2009 10:34:47 AM Date and time are in the same cell (A2). How do I calculate total time off between each LOG_OFF and LOG_ON? I used =INT(D8-D7)&"d "&TEXT(MOD((D8-D7),1),"h:mm:ss"), but it doesn't apply to each row (I'm guessing I need an IIF formula?). Also, the result is "Od 0:00:04" (for example). I'll need the result to not include the "Od." I'm using Excel 2007. Thank you so much. -- MB |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Time
If I were you, I'd just strip out the numbers:
=LOOKUP(99^99,--MID(A14,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A14&"012345 6789")),ROW(INDIRECT("1:256")))) Then, format as 'Custom' mm/dd/yyyy h:mm:ss Then use that function you posted: =INT(B15-B14)&"d "&TEXT(MOD((B15-B14),1),"h:mm:ss") HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "MB" wrote: The data I have is imported into Excel as: A1 A2 LOG_OFF 10/13/2009 09:50:45 AM LOG_ON 10/13/2009 10:00:18 AM CO_Incoming 10/13/2009 10:34:15 AM CO_Incoming 10/13/2009 10:15:39 AM LOG_OFF 10/13/2009 10:15:54 AM LOG_ON 10/13/2009 10:27:13 AM WRAP_ON 10/13/2009 10:34:45 AM WRAP_OFF 10/13/2009 10:34:47 AM Date and time are in the same cell (A2). How do I calculate total time off between each LOG_OFF and LOG_ON? I used =INT(D8-D7)&"d "&TEXT(MOD((D8-D7),1),"h:mm:ss"), but it doesn't apply to each row (I'm guessing I need an IIF formula?). Also, the result is "Od 0:00:04" (for example). I'll need the result to not include the "Od." I'm using Excel 2007. Thank you so much. -- MB |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Time
Hi Ryan. Thank you for your response. I tried the LOOKUP formula and it
didn't work. I don't understand it either, but I'm sure because I'm an intermediate user. -- MB "ryguy7272" wrote: If I were you, I'd just strip out the numbers: =LOOKUP(99^99,--MID(A14,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A14&"012345 6789")),ROW(INDIRECT("1:256")))) Then, format as 'Custom' mm/dd/yyyy h:mm:ss Then use that function you posted: =INT(B15-B14)&"d "&TEXT(MOD((B15-B14),1),"h:mm:ss") HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "MB" wrote: The data I have is imported into Excel as: A1 A2 LOG_OFF 10/13/2009 09:50:45 AM LOG_ON 10/13/2009 10:00:18 AM CO_Incoming 10/13/2009 10:34:15 AM CO_Incoming 10/13/2009 10:15:39 AM LOG_OFF 10/13/2009 10:15:54 AM LOG_ON 10/13/2009 10:27:13 AM WRAP_ON 10/13/2009 10:34:45 AM WRAP_OFF 10/13/2009 10:34:47 AM Date and time are in the same cell (A2). How do I calculate total time off between each LOG_OFF and LOG_ON? I used =INT(D8-D7)&"d "&TEXT(MOD((D8-D7),1),"h:mm:ss"), but it doesn't apply to each row (I'm guessing I need an IIF formula?). Also, the result is "Od 0:00:04" (for example). I'll need the result to not include the "Od." I'm using Excel 2007. Thank you so much. -- MB |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Time
Just do what I said. It works. Trust me. :)
Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "MB" wrote: Hi Ryan. Thank you for your response. I tried the LOOKUP formula and it didn't work. I don't understand it either, but I'm sure because I'm an intermediate user. -- MB "ryguy7272" wrote: If I were you, I'd just strip out the numbers: =LOOKUP(99^99,--MID(A14,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A14&"012345 6789")),ROW(INDIRECT("1:256")))) Then, format as 'Custom' mm/dd/yyyy h:mm:ss Then use that function you posted: =INT(B15-B14)&"d "&TEXT(MOD((B15-B14),1),"h:mm:ss") HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "MB" wrote: The data I have is imported into Excel as: A1 A2 LOG_OFF 10/13/2009 09:50:45 AM LOG_ON 10/13/2009 10:00:18 AM CO_Incoming 10/13/2009 10:34:15 AM CO_Incoming 10/13/2009 10:15:39 AM LOG_OFF 10/13/2009 10:15:54 AM LOG_ON 10/13/2009 10:27:13 AM WRAP_ON 10/13/2009 10:34:45 AM WRAP_OFF 10/13/2009 10:34:47 AM Date and time are in the same cell (A2). How do I calculate total time off between each LOG_OFF and LOG_ON? I used =INT(D8-D7)&"d "&TEXT(MOD((D8-D7),1),"h:mm:ss"), but it doesn't apply to each row (I'm guessing I need an IIF formula?). Also, the result is "Od 0:00:04" (for example). I'll need the result to not include the "Od." I'm using Excel 2007. Thank you so much. -- MB |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Time with Date/Time checking in formula | Excel Worksheet Functions | |||
| Calculating Regular time, overtime and double time | Excel Worksheet Functions | |||
| calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
| Calculating effective time from start/end date+time | Excel Worksheet Functions | |||
Linear Mode
