Date format changing on edit replace
 

I have a download that is putting some dates in with a leading space (where
it is a single digit date). I then need to use the networkdays formula on
this, but as there is a leading space it does not register this as a date. If
I manually edit replace the space with a zero this works fine, but if I use a
macro it causes problems - whether I use the edit - replace, or if I
substitute the first character using some other bit of code.
The problem I have is for dates like 7/06/2005 (7th June 2005). If I edit -
replace manually this become 07/06/2005, which is what I want. If I use some
code on this, it then become 06/07/2005 - which when using networkdays with
todays date it is giving me a negative number as this is in the future!
Can anyone give me some code to replace the leading space with a zero and
keep the correct formatting?

Cheers,
cdb

Date format changing on edit replace
 

In code you can split the date with

Dim YearStr, MonthStr, DayStr as string

YearStr = Format(... , "yyyy")
MonthStr = Month(... , "mm")
DayStr = Day(... , "dd")

DateStr = DayStr & "/" & MonthStr & "/" & YearStr


--
Kaak
------------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513
View this thread: http://www.excelforum.com/showthread...hreadid=377644

Date format changing on edit replace
 

Can't you format the cells using format "dd-mm-yyyy"?
Or in a macro: Selection.NumberFormat = "dd-mm-yyyy"?

With kind regards,
Ton Teun

--
Ton
-----------------------------------------------------------------------
TonT's Profile: http://www.officehelp.in/member.php?userid=4
View this thread: http://www.officehelp.in/showthread.php?t=66407
Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.ph