Date format changing

**luizmachado** · 01-18-2016, 11:11 PM

This code below was supposed to transfer 2 kinds of date data.
If I press "Hoje" (It means "today") ribbon, it shall transfer =Today() function to the date column, but I press the "Outra data" (It means "anoter date") ribbon, it shall transfer the date I want (07/02/15, as the print example) to the date collumn. However, when I enter with any "another date", it brings back to the US format (which is different than mine, brazilian). It should be dd/mm/yy (or dd/mm/yyyy), but it is making it mm/dd/yyyy. (I've already check column date format and it's the brazilian one)

I've already desactivated the autofill option, but it won't work. I think that if each row were independent, there would be no problem.

This is my code: (I know this is only a part of the code, but I guess I won't be necessary. I needed, I can post it here)

'Date radios
            If radiohoje.value = True Then
                Range("B" & lrTest - 1).Formula = "=TODAY()"
            End If

            If radiooutradata.value = True Then
                Range("B" & lrTest - 1).value = DateBox.Text
            End If

This is my workbook: (1st image shows the userform with all that options to fill this table below - date option is there. 2nd image is the problem happening, as you can see by the 1st image date value.)

**luizmachado** · 01-18-2016, 11:16 PM

Sorry, the data on date textbox is actually 07/02/15, not 02/07/15. I, accidentally, deleted the first image, needing to "create" another one and put the wrong data in it. Anyway, in the first image where you see "02/07/15", is, actually, "07/02/15" (that's how it's changing).

Forget it. I fixed!

**spitfireblue** · 01-18-2016, 11:16 PM

You can format the date using the below function...

Format(Date, "dd/mm/yyyy")

i.e. Format (DateBox.Text,"dd/mm/yyyy")

**luizmachado** · 01-18-2016, 11:26 PM

Originally Posted by spitfireblue

You can format the date using the below function...

Format(Date, "dd/mm/yyyy")

i.e. Format (DateBox.Text,"dd/mm/yyyy")

I tried it and it's giving me "Syntax error". I basically copy/paste on that code above.

'Date radios
            If radiohoje.value = True Then
                Range("B" & lrTest - 1).Formula = "=TODAY()"
            End If

            If radiooutradata.value = True Then
                Range("B" & lrTest - 1).value = DateBox.Text
                Format(DateBox.Text, "dd/mm/yyyy")
            End If

**spitfireblue** · 01-18-2016, 11:36 PM

Like this...

If radiooutradata.value = True Then
    Range("B" & lrTest - 1).value = Format(DateBox.Text, "dd/mm/yyyy")
End If

What format is DateBox? You probably don't need the .Text

**luizmachado** · 01-19-2016, 08:50 PM

I tried it out, but it didn't work =/. I'm still getting the date format changed.
DateBox is a textbox on my user form. I tried without .Text and, as you said, it is not needed.

Originally Posted by spitfireblue

Like this...

If radiooutradata.value = True Then
    Range("B" & lrTest - 1).value = Format(DateBox.Text, "dd/mm/yyyy")
End If

What format is DateBox? You probably don't need the .Text

**spitfireblue** · 01-19-2016, 09:06 PM

Can you attach a copy of the workbook?

**luizmachado** · 01-19-2016, 09:16 PM

Here it is =)

Originally Posted by spitfireblue

Can you attach a copy of the workbook?

**spitfireblue** · 01-19-2016, 09:59 PM

Here you go. Your problem was that DateBox was coming through as text

            'Date radios
            If radiohoje.value = True Then
                Range("B" & lrTest - 1).Formula = "=TODAY()"
                Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
            End If

            If radiooutradata.value = True Then
                Range("B" & lrTest - 1).value = DateValue(DateBox)
                Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
            End If

**luizmachado** · 01-20-2016, 10:09 AM

It worked!!!!! Thank you =)

**luizmachado** · 01-30-2016, 01:03 PM

I was working till now, but it won't keep the "dd/mm/yyyy" format for every other sheet.

This is a part of the actual code:

aux = TextBox3.value 'aux = 3
                auxinstnumber = aux
                Do While aux <> 0
                    'Activates next sheet
                    Worksheets(ActiveSheet.Index + 1).Select
                    
                    'Count last row
                    lrTest = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
                    
                    'Resize table row from B21 to J on the last row
                    ActiveSheet.ListObjects(2).Resize Range("$B$21:$K$" & lrTest + 1)
    
                    'Count last row
                    lrTest = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
                    
                    If IsEmpty(Range("C" & lrTest - 1)) Then
                        'Date
                        If ActiveSheet.Name = "Fevereiro" Then
                            Range("B" & lrTest - 1).value = "08/02/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Março" Then
                            Range("B" & lrTest - 1).value = "08/03/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Abril" Then
                            Range("B" & lrTest - 1).value = "08/04/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Maio" Then
                            Range("B" & lrTest - 1).value = "08/05/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Junho" Then
                            Range("B" & lrTest - 1).value = "08/06/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Julho" Then
                            Range("B" & lrTest - 1).value = "08/07/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Agosto" Then
                            Range("B" & lrTest - 1).value = "08/08/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Setembro" Then
                            Range("B" & lrTest - 1).value = "08/09/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Outrobro" Then
                            Range("B" & lrTest - 1).value = "08/10/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Novembro" Then
                            Range("B" & lrTest - 1).value = "08/11/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Dezembro" Then
                            Range("B" & lrTest - 1).value = "08/12/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        'Description
                        Range("C" & lrTest - 1).value = TextBox1.Text

                        'Moves information and delete last row in case data doesn't go to the first row
                        If IsEmpty(Range("C22")) Then
                            Range("C22") = Range("C23")
                            With ActiveSheet.ListObjects(2).DataBodyRange
                            .Rows(.Rows.Count - 1).delete
                            End With
                        End If
                    End If
                    
                    'Deletes C23 row in case last row equals 24 and it's empty
                    lrTest = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
                    If lrTest = 24 And IsEmpty(Range("C23")) = True Then
                        With ActiveSheet.ListObjects(2).DataBodyRange
                        .Rows(.Rows.Count).delete
                        End With
                    End If
                
                    aux = aux - 1
                Loop

**luizmachado** · 01-30-2016, 01:07 PM

It was working till now*

And this is a part of the entire code, if needed:

'Payment radios and total column
            If radiomaster.value = True Then
                'This way there's no need to add "1" when there's no installments
                If radiomaster = True And TextBox3.Text = "" Then
                    TextBox3.Text = "1"
                End If
                Range("F" & lrTest - 1).value = TextBox3.Text
                Me.TextBox2.value = Replace(Me.TextBox2.value, ".", ",") 'Prevent dot error
                Range("E" & lrTest - 1).value = CCur(TextBox2.value)
                Range("J" & lrTest - 1) = Range("E" & lrTest - 1)
                'Balance
                If lrTest = 23 Then
                    Range("K" & lrTest - 1) = Range("F1")
                    Range("K" & lrTest) = Range("F1")
                Else
                    Range("K" & lrTest - 1) = Range("K" & lrTest - 2).value
                    Range("K" & lrTest) = Range("K" & lrTest - 1).value
                End If
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                aux = TextBox3.value 'aux = 3
                auxinstnumber = aux
                Do While aux <> 0
                    'Activates next sheet
                    Worksheets(ActiveSheet.Index + 1).Select
                    
                    'Count last row
                    lrTest = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
                    
                    'Resize table row from B21 to J on the last row
                    ActiveSheet.ListObjects(2).Resize Range("$B$21:$K$" & lrTest + 1)
    
                    'Count last row
                    lrTest = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
                    
                    If IsEmpty(Range("C" & lrTest - 1)) Then
                        'Date
                        If ActiveSheet.Name = "Fevereiro" Then
                            Range("B" & lrTest - 1).value = "08/02/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Março" Then
                            Range("B" & lrTest - 1).value = "08/03/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Abril" Then
                            Range("B" & lrTest - 1).value = "08/04/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Maio" Then
                            Range("B" & lrTest - 1).value = "08/05/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Junho" Then
                            Range("B" & lrTest - 1).value = "08/06/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Julho" Then
                            Range("B" & lrTest - 1).value = "08/07/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Agosto" Then
                            Range("B" & lrTest - 1).value = "08/08/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Setembro" Then
                            Range("B" & lrTest - 1).value = "08/09/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Outrobro" Then
                            Range("B" & lrTest - 1).value = "08/10/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Novembro" Then
                            Range("B" & lrTest - 1).value = "08/11/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        If ActiveSheet.Name = "Dezembro" Then
                            Range("B" & lrTest - 1).value = "08/12/2016"
                            Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"
                        End If
                        'Description
                        Range("C" & lrTest - 1).value = TextBox1.Text
                        'Category
                        Range("D" & lrTest - 1).value = cmbcategoria.value
                        'Master Installments
                        Range("F" & lrTest - 1).value = "1"
                        'Master
                        Range("E" & lrTest - 1).value = (CCur(TextBox2.value)) / (TextBox3.value)
                        'Total
                        Range("J" & lrTest - 1) = ""
                        'Balance
                        If lrTest = 23 Then
                            Range("K" & lrTest - 1).Formula = "=sum(" & Range("F1").value & ",-E" & lrTest - 1 & ")"
                            Range("K" & lrTest).Formula = "=sum(" & Range("F1").value & ",-E" & lrTest - 1 & ")"
                        Else
                            Range("K" & lrTest - 1).Formula = "=sum(K" & lrTest - 2 & ",-E" & lrTest - 1 & ")"
                            Range("K" & lrTest).Formula = "=sum(K" & lrTest - 2 & ",-E" & lrTest - 1 & ")"
                        End If
                            
                        'Moves information and delete last row in case data doesn't go to the first row
                        If IsEmpty(Range("C22")) Then
                            Range("C22") = Range("C23")
                            With ActiveSheet.ListObjects(2).DataBodyRange
                            .Rows(.Rows.Count - 1).delete
                            End With
                        End If
                    End If
                    
                    'Deletes C23 row in case last row equals 24 and it's empty
                    lrTest = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
                    If lrTest = 24 And IsEmpty(Range("C23")) = True Then
                        With ActiveSheet.ListObjects(2).DataBodyRange
                        .Rows(.Rows.Count).delete
                        End With
                    End If
                
                    aux = aux - 1
                Loop
            End If
            'Activates first sheet
            Worksheets(ActiveSheet.Index - auxinstnumber).Select
            lrTest = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

This is my workbook, if needed (larger than 1000kb): https://www.dropbox.com/s/i8gsssg89m...2016.xlsm?dl=0

**luizmachado** · 01-30-2016, 03:47 PM

Oh, sorry!
I forgot to write DateValue before the actual date.
It is, basically:

Range("B" & lrTest - 1).value = DateValue("08/mm/2016") 'mm as month
Range("B" & lrTest - 1).NumberFormat = "dd/mm/yyyy"

Date format changing

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Date format changing

Re: Date format changing

Re: Date format changing

Re: Date format changing

Re: Date format changing

Re: Date format changing

Re: Date format changing

Re: Date format changing

Re: Date format changing

Re: Date format changing

Re: Date format changing

Re: Date format changing

Re: Date format changing

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula for Changing date format 3-12-13 to format d-mm-yy.

[SOLVED] Changing Multiple sheet names by wk ending date for yr but need specific name/date format

Need help changing date to a sortable date format

VB changing date format

date format keeps changing

Formula required for date without changing the date format

Changing Format of Date

Changing date serial numbers to date format

Bookmarks

Bookmarks

Posting Permissions