You guys are awesome.
Please help me with this, i need to get the date out of this file name
C:\Documents and Settings\Rez\Desktop\Excel forum\Queue Performance Phone Daily15_Dec_2009.csv
If you let me know how this works, i will be to do this myself in the future.
cheers,
Mo
John_John wrote:
Try this
05-Nov-09
Try this sue:
=DATEVALUE(TRIM(MID(A1,FIND("DUE",A1)+3,255)))+TIMEVALUE(TRIM(MID(A1,FIND("DUE",A1)+3,255)))
Previous Posts In This Thread:
Picking up dates from within a string
Hi
I have a report that is populated from a data dump from our work management
system. One of the cells will say something like below and will always
contain a date after the word DUE...
VODAFONE P3 INC526642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09
I want to be able to selct only the date (which somethimes will only read
4/11 - I tried the following formula (from this forum) though am coming
unstuck ...
=RIGHT(R2,FIND("DUE",R2&"DUE"))
as it returns the following when run across the above string ...
26642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09
TIA ...
--
Sue Compelling
Try this...
Try this...
=INT(MID(A1,SEARCH("Due",A1)+4,20))
Format as Date
--
Biff
Microsoft Excel MVP
'Extract the date part alone from the string as text string...
'Extract the date part alone from the string as text string...
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" DUE ",A1)+5,255)," ",REPT(" ",12)),12))
'Extract the date and time. Format the formula cell to excel date
format...The result will be dependent on your system date format.
=--TRIM(MID(A1,FIND(" DUE ",A1)+5,255))
If this post helps click Yes
---------------
Jacob Skaria
:
To extract the month/day part of date try=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("
To extract the month/day part of date try
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" DUE ",A1)+5,255),"/",REPT(" ",10),2),10))
If this post helps click Yes
---------------
Jacob Skaria
:
Jacob - that is brilliant -You make excel sing!!!
Jacob - that is brilliant -
You make excel sing!!!
Tell me - if the user has only entered 5/11 as the date - in extracting it -
how do I add on the current year so that it reads 5/11/2009?
Cheers
--
Sue Compelling
:
Thanks for the feedback.
Thanks for the feedback. Try the below
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" DUE ",A1)+5,255),"/",
REPT(" ",10),2),10)) &"/" & YEAR(TODAY())
If this post helps click Yes
---------------
Jacob Skaria
:
Thanks Jacob - did not quite return the right result and I will try and nut
Thanks Jacob - did not quite return the right result and I will try and nut it
out ...
When I got your formula I wondered how on earth it did what it did - and
then I broke it right down in to its' component parts. I feel very proud
that I actually got what each step was acheiving - thanks for sharing this
with me - magic.
--
Sue Compelling
:
Try this
Try this sue:
=DATEVALUE(TRIM(MID(A1,FIND("DUE",A1)+3,255)))+TIMEVALUE(TRIM(MID(A1,FIND("DUE",A1)+3,255)))
Submitted via EggHeadCafe - Software Developer Portal of Choice
Wise for Visual Studio.NET 2003
http://www.eggheadcafe.com/tutorial...9b-a8eaf65a7f0f/wise-for-visual-studione.aspx