MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Creating date objects from multiple cells.


Posted by Chris M. on December 13, 2001 4:31 PM

I'm trying to put a date togeather from different cells. I'll have a day in one cell and a month in another. I want to put them togeather to make a date in one cell. I'm trying to do this in VBA.

Obviously this is wrong, but to try and help you understand what i'm trying to do.

Date1
month = cells(x,1)
day = cells(x+5,4)
year = 2001

Cells(x,2) = Date1

I know this is probably really obvious, but I can't figure it. I'm obviously not a programmer, but I'm trying. Thanks.


Posted by Jacob on December 13, 2001 4:42 PM

Hi

You dont really need vb. Try this.

Lets say you have Day in A1, Month in A2, and Year in C1.

=Date(C1,B1,A1)

Thats it.

Jacob

Posted by Chris M. on December 13, 2001 4:48 PM

Thanks, Jacob, but...

Thanks for the help, but I need to be able to do it in VBA because this is in the middle of an if satement that is part of a loop, hence cells(x,1) etc...
Any Ideas on how I can do it in VBA?

Posted by Juan Pablo G. on December 13, 2001 4:54 PM

Re: Thanks, Jacob, but...

Well, it's practically the same, different function name tho:

Cells(x,2) = DateSerial(year,month,day)

Juan Pablo G. Thanks for the help, but I need to be able to do it in VBA because this is in the middle of an if satement that is part of a loop, hence cells(x,1) etc...

Posted by Jacob on December 13, 2001 5:04 PM

Re: Thanks, Jacob, but...

There are a couple ways to do this. You could have a column in excel put the dates together and then get that value with vb, or you can try this.

Sub Dates()
MyDay = Range("A1").Value
MyMonth = Range("B1").Value
MyYear = Range("C1").Value
MyDate = DateSerial(MyYear, MyMonth, MyDay)
End Sub

Assuming that the dates are in A1:C1 you can modify to you needs.

Hope this helps

Jacob

Posted by Chris M. on December 14, 2001 9:30 AM

Thanks Jacob!

That helped a lot. thanks a bunch!