Date function in VBA

millhouse123

Active Member
Joined
Aug 22, 2006
Messages
335
I currently have code that determines a date based on todays date.
Once the date is determined It is written to a range in the spreadsheet.

In another range I use the above dates in a formula, however it only works if I use it with the date function.

So what I did was write 3 variabls to store the Year, month and Day.

Now I need to use the date function in VBA and these variables to write the date in the date format.

It should look like this once it is writen to excel.

=Date(YearVariable,MonthVariable,DayVariable)

I need insite as to how to write that in VBA.

Thank you for your help.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Can you post your code?

And why not just stick with Date itself?

I.E. Format(Date, "yy-mm-dd")

HTH,

SMitty
 
Upvote 0
I am sort of confused by your question, Probably as you confused by mine.

The code is basically what I included already.

All I want to happend is in vba I want to write a the date formula to a cell.
Each of the argument for the funtion are variables defined in the code.

Basically all I want to know is how to write the code in vba that puts this equasion in a particular cell.

=Date(YearVariable, MonthVariable,Dayvariable)

Each of the variables is a number representing the particular date determined in earlier code.

I already can write 9/15/2006 to a cell but when I refference it with another formula in the sheet it does not work unless I use the date() funtion and that is why I am trying to figure out how to do this.
 
Upvote 0
What is the actual date you want to put on the worksheet?

What code do you currently have, if any?

You mention variables, where are these being assigned values?
 
Upvote 0
Sub datething()
Dim YearVariable As Integer
Dim MonthVariable As Integer
Dim Dayvariable As Integer
YearVariable = 2006
MonthVariable = 9
Dayvariable = 20
Dim i As String
i = (YearVariable & "-" & MonthVariable & "-" & Dayvariable)
ActiveCell.Offset(0, 0).Range("A1").NumberFormat = "yyyy-mm-dd"
ActiveCell.Offset(0, 0).Range("A1") = i
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Replace YearVariable,MonthVariable,Dayvariable with your values.
 
Upvote 0
First off, I would look at why your formula is not recognizing a date. Is it formatted as text, perhaps?

Are you looking for DateSerial?

Perhaps, something like this:

Code:
Sub test()
Dim d As Date
Dim YearV As Integer, MonthV As Integer, DayV As Integer
YearV = 2006
MonthV = 9
DayV = 19
d = DateSerial(YearV, MonthV, DayV)
Range("C1") = d
End Sub
 
Upvote 0

Forum statistics

Threads
1,211,788
Messages
6,103,970
Members
447,888
Latest member
eaydogan

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