Microsoft Excel - Date Format - Asked By Rajender Prasad on 04-Oct-12 08:17 AM

Dear All,

I have the dates like

24/09/2012
18/08/2012

I am unable to change them to
09/24/2012
08/18/2012

Please help me through formula or VBA code is fine.

Regards,
Prasad

You can use the Excel VBA Format Function .

Function FormatDate(inputdate As Date)
 FormatDate = Format(inputdate, "mm/dd/yyyy")
 
End Function


Find the attached Excel Sheet
Range B Contains original values and C contains Formated values using Formula
FormatDate.zip

Thanks this is fine..
I have all the dates in coulmn J and am not passign any thing manually, and I want to perform this for all of them by clicking a button.
I want to assign a Macro.

Regrads,
Prasad

Rajender,

excel is funny sometime when switching from a number to a date adn vise versa I have issues myself getting the formatting to work the way I want it too and not the way excel thinks I want it too.

That said I dont think your "dates" in J are actually dates but probably text or general for formatting I like you tried to simply change formatting to Dates both short and long and custom but nothing seems to work so here is the formula I came up with if you have or can make a new colum for it.

If you just need the systax to look just like 09/24/2012 you can use =(MID(A1,4,2)&"/"&LEFT(A1,3)&RIGHT(A1,4))
If you want the actual date value without using custom formatting you can use =DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,3)&RIGHT(A1,4))

this may give you 41176 for an answer but that mean your cell is formatted for general or number with 0 decimals
once you get the dates like you want them remember to cut and paste using paste special : values and not just cut and paste you want to lose the formula and keep values.
Don

Hello Rajender,
Try this as a macro.

Sub Macro1()
    Columns("J:J").Select
    Selection.NumberFormat = "mm/dd/yyyy"
End Sub

Then you can attach the macro to your button.

Regards

Harry

Harry,

if the date is set as 25/05/2012 and excel does not recognize this as a date then how is this going to switch the format to 05/25/2012 and make it a date. 

I dont know vba but i cut and pasted your macro and it did not work for me.  

what did I do wrong.

Don

Hello Donald,

I am not sure where you get the idea that the dates in question are not dates.  Granted, if the data is there as strings then the situation would be different and would require a different solution.

I guess we would need some feed-back from Rajender to clarify the situation.

Regards

Harry

Hello Donald,

This works with 'string' dates and regular dates but will fall over if there is other text in the range that is selected.

Regards

Harry

Sub macro1()
Dim rngDate As Range
Dim cell As Range

Set rngDate = Range("J1", ActiveSheet.Range("J65536").End(xlUp))

For Each cell In rngDate
    If IsDate(cell.Value) Then
      cell.Value = CDate(cell.Value)
      cell.NumberFormat = "mm/dd/yyyy"
    End If
Next

End Sub

This is working fine..Thanks you so much..
If you dont mind, I have the values in Column I like
'856423
'25462
'210

I want the above to be converted to number.Please help

Regards,
Prasad

Hello Prasad (sorry if Rajender was not the correct form of address),

This seems to do what you want.

Regards

Harry

Sub macro1()
Dim rngNumber As Range
Dim cell As Range

Set rngNumber = Range("I1", ActiveSheet.Range("I65536").End(xlUp))

For Each cell In rngNumber
    If Left(cell, 1) = "'" Then
      cell.Value = Right(cell, Len(cell) - 1)
    End If
Next

End Sub

This is not performing..Please help..

Please cut nad paste your date row J:J to a new sheet and up load it I can fix it and would be glad to do so.
then you can paste it back.

I thnink the issue you are having is that the date you have is not a real date in excel it sees it as a number or a text, and you want it to be a date and you want it to display MM/DD/YYY and not DD/MM/YYYY  I am not sure the macro will do that.

Don

Hello Prasad,

I think I now know what the problem is but I will be out for a time so will have to get back to you later.

Regards

Harry

Greetings Prasad,

 

I'm a novice with Excel, but I may have a very simple solution for you to try.

 

For this example, I’m assuming the dates you listed in your example (such as 24/09/2012) are at cell reference A1 and continue down that column, and I’m inserting my formula in a column next to it.  Just adjust the “A1” reference as needed to fit your spreadsheet and copy the formula down as needed.

 

The basic problem seems to be just reversing the day and month entries, and keeping the year in place, so I just pulled out the LEFT, MID and RIGHT commands and juggled their placement to match your requirements.  I had to add the VALUE function to encompass the entire formula to make the dates actually work as a date.

 

=VALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4))

 

This isn’t an actual date, just a formula that “looks” like a date – it’s just the results of the formula.  If you actually want to go a step further you can highlight the dates with my formula, copy the values, and using the down arrow under the PASTE button in the Excel Ribbon, select “Paste Values” (keyboard shortcut ALT, H, V, V).  This will replace the formula in the cell with the actual value.  If the cell is formatted as “General”, the 9/24/2012 date will appear as “41176”, which is how Excel represents dates.  If you change the cell formatting to short date, long date etc., you would end up with (9/24/2012) and Monday, (September 24, 2012) respectively.

I hope this works...and as I said, I'm a novice, so I'm just taking a shot.

Hello Prasad,

The following should work provided that there are no other strings in the column (for example text headers etc).

Regards

Harry

Sub macro1()
Dim rngNumber As Range
Dim cell As Range

Set rngNumber = Range("I1", ActiveSheet.Range("I65536").End(xlUp))

For Each cell In rngNumber
      If IsNumeric(cell.Value) Then
        If cell.Value > 0 Then
          cell.Value = Val(cell.Value)
        End If
      End If
Next

End Sub

Hey Troy,

You replied to harry... which means Prasad wount get an email from the cafe saying he got answer, but if he is paying attention he should see it.  Several people have tried to help him but without good feedback from him its hard to tell if he got it working or not.
As for your formula it should work for him.  I gave him the same one using datevalue but like I said without good feedback who knows if he got it working or not.

Don

Thanks Don.  I'll post a short reply so the OP should get an e-mail.  I didn't realize I had replied to the wrong person (obviously).  I was just a bit excited I was able to finally help give an answer back to someone on the website here (although a novice answer) instead of always asking the questions.

Prasad, I posted a novice (although working) answer on Friday, but I replied to someone else's post instead of yours, so you didn't get an e-mail reply from the site, so that is the intent of this post - to send you an e-mail update in case you are only monitoring the e-mail replies.