If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Time elapsed calculation
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 |
#2
|
|||
|
|||
Time elapsed calculation
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 |
#3
|
|||
|
|||
Time elapsed calculation
=(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 |
#4
|
|||
|
|||
Time elapsed calculation
=(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 |
#5
|
|||
|
|||
Time elapsed calculation
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 |
#6
|
|||
|
|||
Time elapsed calculation
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 |
#7
|
|||
|
|||
Time elapsed calculation
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 |
#8
|
|||
|
|||
Time elapsed calculation
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 |
#9
|
|||
|
|||
Time elapsed calculation
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 |
#10
|
|||
|
|||
Time elapsed calculation
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 |
Thread Tools | |
Display Modes | |
|
|