Date function in VBA

millhouse123

Active Member
Joined
Aug 22, 2006
Messages
334
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Can you post your code?

And why not just stick with Date itself?

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

HTH,

SMitty
 

millhouse123

Active Member
Joined
Aug 22, 2006
Messages
334
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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?
 

big_mac

Board Regular
Joined
Jul 21, 2006
Messages
167
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.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,579
Messages
5,676,652
Members
419,638
Latest member
GlenMc52

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
Top