Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I edit the formula without changing the format? {=SUM(if. | Excel Worksheet Functions | |||
find and replace for date format | Excel Discussion (Misc queries) | |||
cell format changing when when using search and replace. | Excel Discussion (Misc queries) | |||
cell format changing when when using search and replace. | Excel Discussion (Misc queries) | |||
how do i replace ( with / without changing format to fraction | Excel Worksheet Functions |