Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Help with time/text format

I'm eporting from a source some of the times come out just fine if is longer then one hour if less then one hour.
Example 1:26:54 AM is fine and I can format to 1:26:54
but if its :08:54 it exports with a leading ":" so I would need to get a formula to add "00" before the ":" then convert it to time format
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Help with time/text format

Hi,

Am Wed, 25 Feb 2015 16:54:12 +0100 schrieb Claus Busch:

=IF(LEFT(A1,1)=":",TIME(0,MID(A1,2,2),MID(A1,5,2)) ,TIMEVALUE(A1))


or:
=IF(LEFT(A1,1)=":",TIMEVALUE(0&A1),TIMEVALUE(A1))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Help with time/text format

Not good with VBA But the your last formula worked like a charm thank you as always Claus


On Wednesday, February 25, 2015 at 9:23:37 AM UTC-6, wrote:
I'm eporting from a source some of the times come out just fine if is longer then one hour if less then one hour.
Example 1:26:54 AM is fine and I can format to 1:26:54
but if its :08:54 it exports with a leading ":" so I would need to get a formula to add "00" before the ":" then convert it to time format


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting text format of time/date into Excel time/date for subtr YY san.[_2_] Excel Worksheet Functions 6 February 25th 10 09:27 AM
Time format for text box hoyos Excel Discussion (Misc queries) 4 December 16th 09 05:23 PM
Format Time as Text bpc Excel Worksheet Functions 9 September 15th 08 07:50 PM
change text format to time Hans Excel Worksheet Functions 3 October 18th 07 01:26 PM
convert time imported as text to time format for calculations batfish Excel Worksheet Functions 3 October 27th 05 11:24 PM


All times are GMT +1. The time now is 10:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"