Hi all,

In a cell value (loaded from a .cap file) i have the date, which
need to use for ensuring that (when i work out how to do it), my copie
range goes into the right sheet.

So, i have the date in 1 cell in this format:
Wed 17 Nov 2004 05:34P

Is there any VBA that could almost take apart the value, and extrac
the necessary data?

I was thinking something along the lines of removing the first
characters, keeping the next 11 and removing anything after that.

That would then leave me with 17 Nov 2004, which i would then use fo
the lookup fun thats ahead of me.

Any ideas, or advice?
Thank you so muc

--
druce
-----------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255
View this thread: http://www.excelforum.com/showthread.php?threadid=53450

=INT(A2) and format as a date

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"drucey" wrote in
message ...

Hi all,

In a cell value (loaded from a .cap file) i have the date, which i
need to use for ensuring that (when i work out how to do it), my copied
range goes into the right sheet.

So, i have the date in 1 cell in this format:
Wed 17 Nov 2004 05:34P

Is there any VBA that could almost take apart the value, and extract
the necessary data?

I was thinking something along the lines of removing the first 4
characters, keeping the next 11 and removing anything after that.

That would then leave me with 17 Nov 2004, which i would then use for
the lookup fun thats ahead of me.

Any ideas, or advice?
Thank you so much


--
drucey
------------------------------------------------------------------------
drucey's Profile:
http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=534501

Can you use =MID(A1,5,11) in excel?


--
Jimbo1
------------------------------------------------------------------------
Jimbo1's Profile: http://www.excelforum.com/member.php...o&userid=30637
View this thread: http://www.excelforum.com/showthread...hreadid=534501

Hi,

use this formula in VBA:

date=cdate(mid("Wed 17 Nov 2004 05:34P ",4,len("Wed 17 Nov 2004
05:34P ")-4))

You will get date as result.

In case you want to convert it somehow else, explore type conversion
functions in VBA help.

Regards,
Ivan

with

a="Wed 17 Nov 2004 05:34P"

try
format(mid(a,instr(a," ")),"dd mmm yyyy")

returns

17 Nov 2004

"drucey" wrote:


Hi all,

In a cell value (loaded from a .cap file) i have the date, which i
need to use for ensuring that (when i work out how to do it), my copied
range goes into the right sheet.

So, i have the date in 1 cell in this format:
Wed 17 Nov 2004 05:34P

Is there any VBA that could almost take apart the value, and extract
the necessary data?

I was thinking something along the lines of removing the first 4
characters, keeping the next 11 and removing anything after that.

That would then leave me with 17 Nov 2004, which i would then use for
the lookup fun thats ahead of me.

Any ideas, or advice?
Thank you so much


--
drucey
------------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=534501

Jimbo1 Wrote:
Can you use =MID(A1,5,11) in excel?

Works Perfectly! Thank you Jimbo


--
drucey
------------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=534501