[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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.
1 Solution
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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 ...

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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now