Counting Hours & Days from a Text Download


Posted by David on April 03, 2001 9:54 AM

I am taking a TSO Download in text fromat that has a work order Open date & time and Work order Closed Date and time and importing it into excel. My question is how do I take those (4 cells) 2 open and 2 closed cells and come up with either days and hours or just total hours the Work order has been opened?
All help is greatly appreciated

David

Posted by Dave Hawley on April 03, 2001 10:18 AM


Hi David

Not too sure I understand, but here are a couple of formulas that will extract your Date and Time.

To convert a String date to a real date you can use: =DATEVALUE(A1)
Where A1 contains a text date and time.


To extract the time you can use:
=TIMEVALUE(RIGHT(A1,4))


Then format cells to suit.

Dave

OzGrid Business Applications



Posted by Mark W. on April 03, 2001 10:19 AM

Days open... ='Close Date'+'Close Time'-'Open Date'+'Open Time'
Hours open... =('Close Date'+'Close Time'-'Open Date'+'Open Time')*24