Create DateTime From Year, Mo, Day, Hr and Min fields

I'm doing a conversion of some legacy data.  In the old application the Year, Month, Day, Hour and Minute are all stored in individual fields.
In the new database I would like to create a date field that contains all of those.

In the past I have used the CDate function to convert individual Year, Month and Day into an Access 'Date.

convertDateCCCCMMYY = CDate(wkMMStr & "/" & wkDDStr & "/" & wkCCCCStr)

What is the format to include a Hour and Minute to create an access date field containing the date, hour and minute?  

Similar to the resulting contents when moving 'Now' into a date field, only in this case no seconds are available.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


pls try

convertDateCCCCMMYY = CDate(wkMMStr & "/" & wkDDStr & "/" & wkCCCCStr & " " & wkHHStr & ":" & wkMinutesStr)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DateSerial(year, month, day) will get you the Date
Coerce it to a Double
Times are fractions of a day, so Hour/24 + Minute /(24 *60) will give you the fraction
Add that to the Coerced Double and then CDate() the whole works

Dim TheYear As String
Dim TheMonth As String
Dim TheDay As String
Dim TheHour As String
Dim TheMinute As String

TheYear = 2015
TheMonth = 10
TheDay = 29
TheHour = 9
TheMinute = 48

MsgBox CDate(CDbl(DateSerial(TheYear, TheMonth, TheDay)) + TheHour / 24 + TheMinute / (24 * 60))

Open in new window

gets it done

So does this
MsgBox CDate(TheYear & "-" & TheMonth & "-" & TheDay & " " & TheHour & ":" & TheMinute)

ISO date format is your friend!
Gustav BrockCIOCommented:
String handling should be avoided if at all possible which it is here.

It can and should be done more elegantly the direct way:
DateCCCCMMDDHHNN = DateSerial(wkCCCCStr, wkMMStr, wkDDStr) + TimeSerial(wkHHStr, wkNNStr, 0)

Open in new window

I bow  to the master of Time and Date! :)
Have you written articles that I can point folks to, yet?

Because you know more about this than anyone else (or me anyway!)
It does look like the initial data was string from the variable typing, though.

Gustav BrockCIOCommented:
Oh, if only the day had 48 hours ...

You are right, the variables seem to be strings. We don't know the reason, but if they are only used for this purpose, they should be changed to Integer or Long. As long they contain a numeral, VBA will cast them automatically to Integer.

However, to play safe, you could use Val:

    DateSerial(Val(wkCCCCStr), Val(wkMMStr), Val(wkDDStr))

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.