date format need to require in same format

[manish Joshi07]

dear all.
need to change all the date in same format. i am trying with change the format but it is not taking.

pl suggest any way for convert into same format.

sample sheet attached

[Kamboj]

First make the format of date as date mm/dd/yyyy hh:mm and then use this macro to convert every
cell to your desire format

Please Login or Register  to view this content.

[manish Joshi07]

hi thanks for reply

there may be some sintex error in macro so it is not running.

pl check again and suggest

[cbatrody]

Hi,

This can be done using formula.

In A2 you have 09/08/2015, is it '9th Aug 2015' or '8th Sep 2015' ?

[manish Joshi07]

hi,

it is 9th aug 2015

[cbatrody]

Hi,

Try the following formula in C2:

=IFERROR(A2*1,DATE(MID(A2,FIND("/",A2)+4,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2))+1*RIGHT(A2,LEN(A2)-FIND(" ",A2)))

See the attached file.

[samba_ravi]

C2=B2-IF(ISNUMBER(A2),A2,(MID(A2,FIND("/",A2,4)-2,3)&REPLACE(A2,FIND("/",A2,4)-2,3,))+0)

Please Login or Register  to view this content.

Try this and copy towards down

[Kamboj]

First select col a and then right click and select format Cells
Under Number Choose the category Custom and then under type write ->

Please Login or Register  to view this content.

<-
and press ok
after that run this macro

Please Login or Register  to view this content.