Time elapsed calculation

[MTate]

I have a text string in a cell that comes from an AS400. I need for this
string to convert to a time value.

Example text: 20100409073000
In the AS400 this represents the date of 4/9/2010 and 7:30 AM. So
YYYYMMDDHHMMSS is the format.

What I need to do is extract the 073000 part off and convert it to an actual
time, so it can then be compared to the system time using the Now function to
calculate the time elasped. The time elapsed would need to show in number
format, so 4 hours and 15 minutes would be 4.25

Any help would be appreciated.

 

[Bob Phillips]

Try

=(MOD(NOW(),1)-TIME(MID(A20,10,1),MID(A20,11,2),0))*24

 

[David Biddulph]

=(NOW()-TEXT(A2,"0000\-00\-00 00\:00\:00"))*24 and format the result as
General or Number.

 

[Teethless mama]

=(MOD(NOW(),1)-TEXT(RIGHT(A1,6),"00\:00\:00"))*24

 

[Ron Rosenfeld]

I have a text string in a cell that comes from an AS400. I need for this
string to convert to a time value.

Example text: 20100409073000
In the AS400 this represents the date of 4/9/2010 and 7:30 AM. So
YYYYMMDDHHMMSS is the format.

What I need to do is extract the 073000 part off and convert it to an actual
time, so it can then be compared to the system time using the Now function to
calculate the time elasped. The time elapsed would need to show in number
format, so 4 hours and 15 minutes would be 4.25

Any help would be appreciated.

Because of the possibility of the difference extending over midnight, I would
suggest you convert the entire string into a date/time string:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))

To compare it with the system time:

=(NOW()-(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))))*24

and format the result as Number with two decimals.
--ron

 

[Ron Rosenfeld]

=(NOW()-TEXT(A2,"0000\-00\-00 00\:00\:00"))*24 and format the result as
General or Number.

Note that, although that will work with US based systems, if the Windows
Regional format is set to some of the non-US formats, this will not translate
correctly.
--ron

 

[David Biddulph]

I'm surprised at that, Ron. I'm in the UK, not the US, and I thought that
yyyy-mm-dd was accepted universally. I know that there are problems with
dd-mm-yyyy and mm-dd-yyyy, but I hadn't come across problems with
yyyy-mm-dd.

 

[Bob Phillips]

yyyy-mm-dd should be okay, it is the ISO standard format. It is what I use
in my SP solutions precisely beciuse it seems the one format not to cause
problems.

 

[Ron Rosenfeld]

I'm surprised at that, Ron. I'm in the UK, not the US, and I thought that
yyyy-mm-dd was accepted universally. I know that there are problems with
dd-mm-yyyy and mm-dd-yyyy, but I hadn't come across problems with
yyyy-mm-dd.

David,

I'm based in the US and the formula works OK with US settings.

However, if I go to the Windows Control Panel, and change my format, on the
Format tab, to French(France), your formula changes, appropriately, to:

=(NOW()-TEXT(A2;"0000\-00\-00 00\:00\:00"))*24

(Windows 7-64bit; Excel 2007)

but the result is #VALUE!

If I then change to English(UK), the formula calculates correctly.

--ron