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.


--
Thanks

Try

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

--

HTH

Bob

"MTate" wrote in message
...

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.


--
Thanks

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


"MTate" wrote in message
...

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.


--
Thanks

On Fri, 9 Apr 2010 20:15:36 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

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

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

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 Biddulph


"Ron Rosenfeld" wrote in message
...
On Fri, 9 Apr 2010 20:15:36 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

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

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

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.

--

HTH

Bob

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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 Biddulph


"Ron Rosenfeld" wrote in message
...
On Fri, 9 Apr 2010 20:15:36 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

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

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

On Sat, 10 Apr 2010 11:12:25 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

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 Biddulph

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

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



"MTate" wrote:


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.


--
Thanks

But beware that you couldn't use that to get an elapsed time spanning
midnight.
--
David Biddulph


"Teethless mama" wrote in message
...
=(MOD(NOW(),1)-TEXT(RIGHT(A1,6),"00\:00\:00"))*24



"MTate" wrote:


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.


--
Thanks

On Fri, 9 Apr 2010 10:51:02 -0700, MTate
wrote:


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