Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 30
Default I need to add space to convert DATE to TIME format

A2: 4/03 6:36pm
B2: FORMULA HERE

In B2, I need to convert A2 to 4/3/07 6:36 pm - putting a space between the
time and PM.

Thanks!

--
Texas Wannano
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default I need to add space to convert DATE to TIME format

custom format B2 as

'mm/dd/yyyy hh:mm AM/PM' (no quotation marks)

HTH

"Wannano" wrote:

A2: 4/03 6:36pm
B2: FORMULA HERE

In B2, I need to convert A2 to 4/3/07 6:36 pm - putting a space between the
time and PM.

Thanks!

--
Texas Wannano

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default I need to add space to convert DATE to TIME format


'mm/dd/yyyy h:mm AM/PM' (no quotation marks)



"Toppers" wrote:

custom format B2 as

'mm/dd/yyyy hh:mm AM/PM' (no quotation marks)

HTH

"Wannano" wrote:

A2: 4/03 6:36pm
B2: FORMULA HERE

In B2, I need to convert A2 to 4/3/07 6:36 pm - putting a space between the
time and PM.

Thanks!

--
Texas Wannano

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default I need to add space to convert DATE to TIME format

One way

=LEFT(A2,FIND(" ",A2))+SUBSTITUTE(MID(A2,FIND(" ",A2)+1,255),"pm"," pm")

format custom as mm/dd/yy hh:mm AM/PM


--
Regards,

Peo Sjoblom



"Wannano" wrote in message
...
A2: 4/03 6:36pm
B2: FORMULA HERE

In B2, I need to convert A2 to 4/3/07 6:36 pm - putting a space between
the
time and PM.

Thanks!

--
Texas Wannano



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 269
Default I need to add space to convert DATE to TIME format

Do you mean you want B2 to have the same information as A2, with a
different format? If so, put =A2 in B2. Select cell B2, then Format
from the menubar, then Cells. On the Number tab, select the Custom
category. In the Type box, type in
m/d/yy hh:mm AM/PM
(leaving a space before AM/PM). I hope I understood your question
correctly. James


On Apr 11, 11:32?am, Wannano
wrote:
A2: 4/03 6:36pm
B2: FORMULA HERE

In B2, I need to convert A2 to 4/3/07 6:36 pm - putting a space between the
time and PM.

Thanks!

--
Texas Wannano





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 30
Default I need to add space to convert DATE to TIME format

yes. Got it.

Thanks all!
--
Texas Wannano


"Zone" wrote:

Do you mean you want B2 to have the same information as A2, with a
different format? If so, put =A2 in B2. Select cell B2, then Format
from the menubar, then Cells. On the Number tab, select the Custom
category. In the Type box, type in
m/d/yy hh:mm AM/PM
(leaving a space before AM/PM). I hope I understood your question
correctly. James


On Apr 11, 11:32?am, Wannano
wrote:
A2: 4/03 6:36pm
B2: FORMULA HERE

In B2, I need to convert A2 to 4/3/07 6:36 pm - putting a space between the
time and PM.

Thanks!

--
Texas Wannano




  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 30
Default I need to add space to convert DATE to TIME format

That worked. Thanks!
--
Texas Wannano


"Peo Sjoblom" wrote:

One way

=LEFT(A2,FIND(" ",A2))+SUBSTITUTE(MID(A2,FIND(" ",A2)+1,255),"pm"," pm")

format custom as mm/dd/yy hh:mm AM/PM


--
Regards,

Peo Sjoblom



"Wannano" wrote in message
...
A2: 4/03 6:36pm
B2: FORMULA HERE

In B2, I need to convert A2 to 4/3/07 6:36 pm - putting a space between
the
time and PM.

Thanks!

--
Texas Wannano




  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 30
Default I need to add space to convert DATE to TIME format

I need to incorporate an OR function...the suffix can be either AM or
PM...the formula did not work where there was an am in the cell. There are
too many line to do it individually.

Advice please.

Thank you!
--
Texas Wannano


"Peo Sjoblom" wrote:

One way

=LEFT(A2,FIND(" ",A2))+SUBSTITUTE(MID(A2,FIND(" ",A2)+1,255),"pm"," pm")

format custom as mm/dd/yy hh:mm AM/PM


--
Regards,

Peo Sjoblom



"Wannano" wrote in message
...
A2: 4/03 6:36pm
B2: FORMULA HERE

In B2, I need to convert A2 to 4/3/07 6:36 pm - putting a space between
the
time and PM.

Thanks!

--
Texas Wannano




  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default I need to add space to convert DATE to TIME format

Sure

=LEFT(A2,FIND(" ",A2))+SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("
",A2)+1,255),"pm"," pm"),"am"," am")

should take care of am as well


--
Regards,

Peo Sjoblom



"Wannano" wrote in message
...
I need to incorporate an OR function...the suffix can be either AM or
PM...the formula did not work where there was an am in the cell. There
are
too many line to do it individually.

Advice please.

Thank you!
--
Texas Wannano


"Peo Sjoblom" wrote:

One way

=LEFT(A2,FIND(" ",A2))+SUBSTITUTE(MID(A2,FIND(" ",A2)+1,255),"pm"," pm")

format custom as mm/dd/yy hh:mm AM/PM


--
Regards,

Peo Sjoblom



"Wannano" wrote in message
...
A2: 4/03 6:36pm
B2: FORMULA HERE

In B2, I need to convert A2 to 4/3/07 6:36 pm - putting a space between
the
time and PM.

Thanks!

--
Texas Wannano






  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 30
Default I need to add space to convert DATE to TIME format

THANKS A MILLION!!!
--
Texas Wannano


"Peo Sjoblom" wrote:

Sure

=LEFT(A2,FIND(" ",A2))+SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("
",A2)+1,255),"pm"," pm"),"am"," am")

should take care of am as well


--
Regards,

Peo Sjoblom



"Wannano" wrote in message
...
I need to incorporate an OR function...the suffix can be either AM or
PM...the formula did not work where there was an am in the cell. There
are
too many line to do it individually.

Advice please.

Thank you!
--
Texas Wannano


"Peo Sjoblom" wrote:

One way

=LEFT(A2,FIND(" ",A2))+SUBSTITUTE(MID(A2,FIND(" ",A2)+1,255),"pm"," pm")

format custom as mm/dd/yy hh:mm AM/PM


--
Regards,

Peo Sjoblom



"Wannano" wrote in message
...
A2: 4/03 6:36pm
B2: FORMULA HERE

In B2, I need to convert A2 to 4/3/07 6:36 pm - putting a space between
the
time and PM.

Thanks!

--
Texas Wannano






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
convert 7h 40.80n into time format Todd Excel Worksheet Functions 1 August 9th 06 01:33 AM
Help: How do I convert a text date into a real date format japorms Excel Worksheet Functions 4 August 2nd 06 06:36 PM
convert time imported as text to time format for calculations batfish Excel Worksheet Functions 3 October 27th 05 11:24 PM
How can I convert a date format to an ISO week format (in EXCEL)? ELI Excel Discussion (Misc queries) 2 July 6th 05 06:31 PM
Convert date to length of time in months from set date MJUK Excel Worksheet Functions 1 March 19th 05 06:31 PM


All times are GMT +1. The time now is 09:55 PM.

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"