Change Custom number type to Date number type

[Villalobos]

Hello,

I would like to ask some help how is it possible to change the "Custom" number type to "Date" in dynamic range.

I have attached the sample file.

Thank you in advance the help.

 

[Khalid NGO]

Hi,
Can you check this formula in Sheet1 E9:
=DATE(LEFT(D9,4),MID(D9,FIND(".",D9)+1,2),RIGHT(D9,2))
Change format to Date...
Copy down.


Regards,

 

[Khalid NGO]

In addition to above, this is also working at my system dd-mm-yyy:
=DATEVALUE(SUBSTITUTE(D9,".","/"))

 

[Deepak]

=DATEVALUE(SUBSTITUTE(D9,".","-"))

or

=TEXT(DATEVALUE(SUBSTITUTE(D9,".","-")),"DD-MMM-YY")

 

[NARAYANK991]

Hi ,

If you want a VBA solution , see the attached file.

Narayan

 

[Villalobos]

Hello Narayan,

Thank you the file, but my problem still open.
My target is to sumifs the turnover in the month (on sheet2) but due to format of the delivery date is not possible. But if I type manually the delivery date on sheet1 the formule calculate as I expect (on sheet2).

Could you tell me please where is the problem?

 

[NARAYANK991]

Hi ,

You need to go through the code to understand what is happening.

Your data is being overwritten by the code , since it is using a helper column to calculate the formula output. I have revised my code.

You need to revise your code , since the output formula is being calculated only for one row ; it has to be done for all the output rows.

I suggest you eliminate this step from your code and use fixed formulae instead.

Narayan

 

[Villalobos]

Thank you the advice but I am sad to read this because i have to handle more thousand materials (number of the materials are changing day by day) in one shot and I want to avoid that to extend the range of the formula manually.

But if there is not solution... I change manually.

 

[NARAYANK991]

Hi ,

I am not able to understand your problem ; the extension that you want can be done , but not by the code you have posted ! That code has to be rewritten to do what you want.

If you can explain in detail , and post a file which has a larger data set , that code can be written.

Narayan

 

[Villalobos]

I figured out, I placed the date as value in other cell and working well.
Thank you the direction.