combining date and time

[h_aesa1]

If i use the following:

CDate(Now()) then i get 4/28/2011 12:38.....

How can I get the same format when cell A1 has the date and cell A2 has the time?

I have tried combining the date and time but to no avail.

Thanks,

JJ

[tlafferty]

If I enter 4/28/2011 in A1 and 12:38:00 PM in B1 and the formula =A1+B1 in cell C1, I get 4/28/2011 12:38

[Leith Ross]

Hello h_aesa1,

In VBA the code would be...

Please Login or Register  to view this content.

The date in VBA is represented as a decimal fraction. The date is the integer portion (left side of the deicmal point) while the time (seconds since midnight) is the decimal portion (right side) of the decimal point. Adding the 2 cells returns a decimal fraction that will be converted in the Short Date format for your system.

[h_aesa1]

thank you for the reponses.. had a quick question

Say i was to use a userform to input to a sheet

so

ComboBox1 = Date
ComboBox2 = Time
ComboBox3 = AM or PM

I believe I can do the Sheet1.Range("A1").value = CDate(ComboBox1) + (TIME) but how do I combine the ComboBox2 with the ComboBox3 to get the full time display

Thanks,

JJ

[Leith Ross]

Hello JJ,

You can combine all the text together before you convert the string and save it in Range("A1").

Please Login or Register  to view this content.

[snb]

It depends on the international settings of your system:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

But i'd prefer a datepickercontrol to comboboxes to select a date & Time

Please Login or Register  to view this content.

[Leith Ross]

Hello snb,

CDate is internationally aware.

[h_aesa1]

I am getting a run time error, run time error '13' Type Mismatch,

Here is the code I am using:

cbxDate = combo box where they can select the date
cbxTime = combo box where times are listed
cbxAMPM = choose AM or PM

Not sure if I have a typo but I copied the exact code from Leith, just different names

Thanks,

JJ

[Leith Ross]

Hello JJ,

It would help to see your workbook. Can you post a copy of it?

[h_aesa1]

Had another question....

I was using an older Calendar Control... so it was only using the calendar... but I was able to look into the DatePicker control and have that as well.

I think this might be a better option but... Is there anyway where you can choose the date in one control and then choose the time in another control.

What I am trying to accomplish is for someone be able to choose a date and then choose a time and format both choices to the following

4/28/2011 13:30

and then put in say Sheet1.Range("A1")


thanks,

JJ

[snb]

Open the attachment (enable macros).

[h_aesa1]

thanks SNB... i like the setup but what would be code to take the results and put into A1 in the following format. 5/3/2011 14:30

So if they choose 5/3 for the date and then choose 2:30 pm as the time... i want them to be able to hit a command button (which i can do) and put into ("A1").... 5/3/2011 14:30


Currently I use

and it puts it into the above format... i just want them to be able to choose and accomplish the same thing.

Thanks,

JJ

[h_aesa1]

to further expand on previous post.

I went into your form and put a command button that shows a msgbox for the value of DTPicker1 and DTPicker2

I chose the Date of 5/3/2011 and the time of 9:30 am

then

Msgbox 1 shows = 5/3/2011
Msgbox 2 shows = 12/17/2007 9:30:00 AM

So obviously this is not going to work when trying to combine them... is there anyway to send the Date of DTPicker1 to the value of DTPicker2 so it would reflect... 5/3/2011 9:30:00 AM


JJ

[snb]

Have a look at the atttachment.

[h_aesa1]

that is Awesome... thank you so much!!!!

Take Care,

JJ

[snb]

I will.

to download the DTPicker look for MSComCtl2.ocx