changing the format on a date to text. 1

[apeoortje]

I have formulars in a sheet which workout the begin and end date of the weeks in a month. unforunatly this info almost spreads itself randomly across the page.

So I have a macro which copies the data and puts it in a cell. I use the number to create a week total in a report. with the code

wk1a = Range("D2").Value

Rows("36:36").Select
Selection.Replace What:="101", Replacement:=wk1a + 4, LookAt:=xlPart, SearchOrder:=xlByRows,

but the end and begin date need to be numbers and not dates for this to work.

does anyone know how?!?

Thx

Terry

 

[SkipVought]

Terry,

Real Excel Dates ARE JUST NUMBERS.

WHat happens if you FORMAT one of your Date Cells as GENERAL -- does it become a number?

If not you have TEXT that you need to CONVERT to a Real Date. What is the form of this pseudo-date? You can use the DATE function
[tt]
=Date(Year,Month,Day)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[apeoortje]

Thx but what I'm looking for is how to turn 01/06/04 in just 01. I've tried just getting excell to show day only but that doesn't work.

Any Ideas?!?

Thx

T

 

[SkipVought]

how to turn 01/06/04 in just 01

"in just 01" WHAT?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[apeoortje]

Sorry How do I turn the date 01/07/04 to just display 01

 

[SkipVought]

If you date is REALLY a DATE...
[tt]
=IF(MONTH(A1)<10,"0","")&MONTH(A1)
[/tt]
if A1 contains your date and the format is mm/dd/yy.

OR

you could simply FORMAT the DATE using a Custom Format "dd"



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[apeoortje]

I've managed in get it!!! I was being stupid, I should have writen =if(E2=7,"01","")
and not =if(E2=7,D2,"")

But thank very much!!!!

 

[SkipVought]

You were NOT very clear at all. Your last post makes ABSOLUTELY NO SENSE in the context of your previous posts!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[apeoortje]

it was my stupid mistake in excel!!!

In the D Collum I have 01-07-04.

In the E collum I have =Weekdays(D2)

and the C I have = if(E2=7,"D2","") at excel set up to displace only the day (DD)

So it all my own stupid fault!!! Sorry for taking up your time!!!! Thanks Mate