Excel formula in VBA code?

behedwin

Active Member
Joined
Dec 10, 2014
Messages
399
Hi

I need some help to figure out how to calculate a date based of a set of numbers.

I have a large table.
each row in the table represent a userprofile with name, adress and other information.
One cell contain a birth number, it is always formated YYMMDDXXXX (xxxx = national numbers for the birthnumber)

I want to print a date based on the 6 first numbers.
Example: the birth number is 801031XXXX
then i want a date 1980-10-31

I want this done in VBA code
I could use some kind of formula... but not sure how to do this with VBA.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Don't know about VBA, but this will extract and give you the date...
=DATE(IF(--LEFT(A19,2)>50,1900,2000)+LEFT(A19,2),MID(A19,3,2),MID(A19,5,2))
Format as needed to get it looking how you want
 
Upvote 0
Tried a code like this

Code:
Dim dat As String
dat = Sheets("Register").Cells(RowIndex, 2).Value
dat = Left(dat, 6)
Sheets("register").range(RowIndex, 89).Value = dat

but it wont work... not sure why :p

idea is to take the value from rowindex 2 and then extract the first 6 numbers and then post those 6 numbers in rowindex 89
 
Upvote 0
moved past that i think...

got this now

Code:
    Dim ckval
    ckval = UserForm4.TextBox_birthyear
    If Not IsDate(ckval) Then
        ckval = Left(UserForm4.TextBox_birthyear, 6)
        ckval = Left(UserForm4.TextBox_birthyear, 6)
        ckval = Format(ckval, "yyyymmdd")
    End If
    UserForm4.TextBox1.Text = ckval

the value in textbox_birthyear looks like this: 8301310095
then ckval extracts the 6 first digits.
now i want to present those 6 digits as a date.....

1983-01-13

but my format of this does not seem to work.
not sure how to fix this.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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