VBA: Converting date from dd/mm/yyyy to yyyymmdd

Nihonjin

New Member
Joined
May 7, 2005
Messages
46
Hi,

need your help. A report needs to name a sheet according the current business date.

cob = 02/06/2008
ActiveSheet.Name = cob

Due to the '/' the macro shows an error.
As the cob is needed in this specific format for other parts in the macro, is there any command how the format can be changed to 20080602 for renaming the sheet?

i.e.
cob = 02/06/2008
ActiveSheet.Name = Numberformat(yyyymmdd, cob)

Any help is is very welcome.

Thanks
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi there,

Not sure how you're assigning the "date" to the cob variable, but let me know if this is what you're after (or otherwise):

Code:
strYear = Year(cob)

    If Len(Month(cob)) = 1 Then
        strMonth = "0" & Month(cob)
    Else
        strMonth = Month(cob)
    End If
    
    If Len(Day(cob)) = 1 Then
        strDay = "0" & Day(cob)
    Else
        strDay = Day(cob)
    End If
    
ActiveSheet.Name = strYear & strMonth & strDay

HTH

Robert
 
Upvote 0
Hi,
I have to assuming that cob is a string, since Excel will otherwise treat the formula you entered as 2 divided by 6 divided by 2008. With that assumption, the following code will accomplish what you need. The first option allows for entry of single-digit month and day; if you always have 2 digits for month and day, the second option is a much simpler solution:
Option 1:
Code:
cob = "2/6/2008"
cobYear = Right(cob, 4)
cobDay = Format(Left(cob, InStr(1, cob, "/") - 1), "00")
cobMonth = Format(Mid(cob, InStr(1, cob, "/") + 1, InStr(InStr(1, cob, "/") + 1, cob, "/") - InStr(1, cob, "/") - 1), "00")
ActiveSheet.name = cobYear & cobMonth & cobDay
Option 2, if you ALWAYS have 2 digits entered for each day and month:
Code:
cob = "02/06/2008"
ActiveSheet.name = Right(cob, 4) & Mid(cob, 3, 2) & Left(cob, 2)
Hope this helps,
Cindy
 
Upvote 0
Dates are, I believe, the worst nightmire in Excel and Access.
I had so much trouble in the past with dates for applications to be use in several countries, that I renouced definitively to rely on built-in dates in Excel or (even more) in Access.
Either I am now using the yyyymmdd format, or I ask for yyyy, mm and dd separately.

Anyway, assuming your date will be correctly recognized, I would simply suggest using the following formula:

yyyymmdd = 10000*year(cob) + 100*month(cob)+day(cob)

(this is more readable than the serialDate ;>} )
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top