How to change date format for the specific cell range.

[uday]

I want to create one excel file wherein date format will be fixed in dd/mm/yy..whenever I am trying to give anyother date format.

If you open my attachment you can find in B column that date format is mention with timestamp but I do not want to see the data in that format..
Everyday I have to copy and paste the data from one raw file wherein date format is comes up with the time stamp i.e, dd/mm/yy hh:mm:ss but its wrong..I need to change it to only dd/mm/yy for the specific column cell range whenever I am giving the data in that column. Please do the procedure in VBA..
Please find the attachment.

 

[Faseeh]

Hi Uday,

Solution is very simple. When you are pasting data, paste as values only. This will keep the formatting of destination cell and does not require any vba. Hope that helps.

 

[uday]

Thanks for you suggestion ..but when I am pasting the data in values its automatically change to serial number i.e,
41721.3
...The file which I am concerning about ,its handled by many novice people in excel..and every time its not possible to show my presence or assist them.....and above all I want to do this in vba which would give me an automatic assistance.

 

[Faseeh]

Okk. VBA is not my area of expertise. so you can waiting till someone else responds. thank you.

 

[NARAYANK991]

Hi Uday ,

Can you clarify whether you wish to change the format , or do you wish to change the value itself ?

In the sample data you have shown , it is not the format which has changed ; the values in column D are integers , while the values in column B are decimal values , since they have a time component also.

Narayan

 

[uday]

My target is to change the date format from dd/mm/yy hh:mm:ss to dd/mm/yy....for a specific cell range.

 

[NARAYANK991]

Hi Uday ,

I am still wondering what you wish to do ; a single statement as follows :

Selection.Numberformat = "dd/mm/yy"
or
Selection.Numberformat = "dd/m/yy"

will convert the selected range to the specified format.

Narayan

 

[uday]

Narayan...

We are extracting one raw file in daily basis wherein one column contains details data of scan date..but the this information also comes with time stamp like hh:mm:ss.This time details is not visible but only if you select that particular columns any cell.
We need to copy and paste that raw data into another file wherein only date format needed in scan dates column .
i.e, dd/mm/yy..not with time stamp i.e,hh:mm:ss..

My target is when I am copy and pasting the data from the raw file to another worksheet it will be automatically converted to dd/mm/yy..I don't need any time details..
Please reply

 

[NARAYANK991]

Hi Uday ,

You have explained the process that is followed ; is this done manually or by using a macro ?

Do you want that the reformatting be done separately after the copy + paste is done ? If so , as I posted earlier , just execute the one statement.

Narayan

 

[uday]

Narayan...I can not able to execute the vba code...Can u please do it for me in attachment.

Please reply

 

[NARAYANK991]

Hi Uday ,

I am still not sure about what you want ; anyway check this file ; go through the macro before you run it.

Narayan

 

[uday]

Narayan thanks for reply.
I can not able to see any changes....
Ok..I m trying to explain it again ..If I copy the highlighted data of B column (B4:B7) TO column D(D4:D7) it will be automatically change from dd/mm/yy hh:mm:ss to only dd/mm/yy.
and please open my latest attachment

 

[uday]

Sorry ...column B is not highlighted...

 

[NARAYANK991]

Hi Uday ,

Let me explain , in case you do not know ; a .xlsx file cannot have macros ; a .xlsm has.

I uploaded a file which had a macro in it ; I specifically requested you to go through the macro before running it ; have you done so ? If you had , you would not again upload the .xlsx file.

I am again requesting you to go through the macro in the .xlsm file I uploaded , and try and understand the two or three lines of code there. If you still have difficulties in doing what you want , let me know.

Narayan

 

[uday]

Sorry I have wrongly uploaded the xlsx file.

 

[NARAYANK991]

Hi Uday ,

I have uploaded the file again ; check now.

Narayan

 

[uday]

Thanks for your work..but still when I am trying to copy paste the data from the B column to column d ..date formate is still remain with the time details..after copy pasting if you click on the pasted cell of column d ..you can see the same format as b..dd/mm/yy hh:mm:ss...it remain same

 

[NARAYANK991]

Hi Uday ,

We are back to square one ; please confirm the option you want :

1. do you want only the format alone to be dd/mm/yy

2. do you want the value itself to be changed from a date & time combination to a date alone ? Just for the record , this is a question I asked 6 posts back , about 3 hours ago.

Narayan

 

[uday]

Yes,narayan....
Your no\ 2. point is right..I want the value itself to be changed from a date & time combination to a date alone if I copy paste the data from the raw file.

 

[Somendra Misra]

Hi Uday,

Sorry for stepping In between a conversation. I did not understand the use of excluding the time portion from the stamp. If you just format a complete column as d-m-yy and paste the values only you will get in the required format.

If you want to do certain calculations on these dates in any formula, instead of using the date cell you can use INT(A1), supposing A1 contain the date stamp in the format.

In this way your file viewers can see only dates, you can fix the column width also and in calculations you will get the right result.

Regards,

 

[uday]

Yes, I have to do certain calculation.....but its not working with time stamp details...Previously in our raw data we we were getting only date..but now we are getting date & time which is not acceptable by the formula..and giving me only #REF! texts ..

If you open the attachment you can see in column J first column contain date & time stamp.....but whenever you change that to only date it will automatically calculate by the formula and giving me the right result.

 

[Somendra Misra]

@uday

Try changing formula to :

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(J7)&":"&INT(K7))),2)<6))

Regards,

 

[NARAYANK991]

Hi Uday ,

Sorry , but I think I did not change the column from F to D. Check the file now.

Narayan

 

[uday]

Hi All,

Thanks.....Now its working perfectly......