Adding hours to date/time

I need a function with parameters as follows

a = addhours("15/12/1999 10:07:23 PM",1)
This would return the time/date specified + 1 hour so it would be:
15/12/1999 11:07:23 PM

I can do this with string manipulation, but the trouble is when adding large amounts of hours, or adding negative figures.
If I want to set the time back 3 hours I want to be able to do
a = addhours("15/12/1999 10:07:23 PM",-3)

and also it must change the date if necessary. So if for example the hours forward are 37, then the day would obviously go forward.

This is more complicated than it first seems, especially setting forward the month/year etc..

The time should be in the format of 'now'

If you do:
msgbox now
you will get the correct date/time format
(what I mean by this is that it will change from dd/mm/yyyy to mm/dd/yyyy in USA. It MUST work for both.
Who is Participating?
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.

plasmatekAuthor Commented:
Please leave comments only, unless you are 100% certain your answer is working and correct, as I don't want to lose my points.
Put this function in a MODULE in your program:

Function AddHours(lDate As Date, NumberOfHours As Long) As Date
    AddHours = lDate + (0.00001157409 * CLng(3600) * NumberOfHours)
End Function

Then you can call it like this:

   Dim ctime1 As Date
   Dim ctime2 As Date

   ctime1 = Now
   ctime2 = AddHours(ctime1, 2)
   Debug.Print ctime1, ctime2

Ctime2 will be 2 hours ahead of ctime1



This simple function should work perfectly...

Function AddHours(d As String, h As Long) As Date
  AddHours = CDate(d) + (h / 24)
End Function

Try it:
Sub TryItOut()
  Dim a As Date
  a=AddHours("15/12/1999 10:07:23 PM",1)
  MsgBox a
  a=AddHours("15/12/1999 10:07:23 PM",-3)
  MsgBox a
  a=AddHours("15/12/1999 10:07:23 PM",37)
  MsgBox a
End Sub

Ture Magnusson
Karlstad, Sweden
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

You can also use my example code this way:

a = addhours(CDate("15/12/1999 10:07:23 PM"),-3)

Be careful when using strings passed to CDate, as that function may return a different date depending on what date format windows is set to use.  For example, what's the month for the following?

CDate ("2/3/1999 10:07:23 PM")

Is it Feburary or March?

If you are using date constants in your program, use the ## notation, so your constant will be compiled correctly based on the computer where you compiled it.

Const MYDATE as Date = #2/3/1999 10:07:23 PM#

Also, the standard DateAdd function in VB may be useful in this case, rather than doing raw arithmetic on hours, as previously suggested.

NewDate = DateAdd("h", 3, MYDATE) ' Add 3 hours
OldDate = DateAdd("h", -4, MYDATE) ' Subtract 4 hours
.... etc ...


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
plasmatekAuthor Commented:
Seems I offered too many points for this fairly simple question when there is a vb function to do it. But thanks for the help.

Todd's answer seems best as it uses standard vb functions so he gets the points
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
Visual Basic Classic

From novice to tech pro — start learning today.