CODEWITHSUNDEEP
excelvbaexcel-formula
Lalaland
Lalaland

Reputation: 308

Adding Days and hours to current time

I have written some code which I then use in a message box to take the current date/time and i then choose how many days/hours/min to add to current date and time and it pops up.

If i only chose day as 1 extra it works perfect but the moment i chose 1 day and 1 hour then it adds 2 days to the date. If i say 1 day, 1 hour and 1 min then it adds 3 days to current date/time.

Here is my function code

Function HoursLost()
Dim Day As Date
Dim Hour As Date
Dim Minute As Date
Dim LValue As Date

LValue = Now
   
   Day = InputBox("Enter Day ", "Enter Number of Days")
   Hour = InputBox("Enter Hour", "Enter Number of Hours")
   Minute = InputBox("Enter Minute", "Enter Number of Minutes")
   HoursLost = Now + Day + Hour + Minute
   
End Function

And here is my code to call the box

Option Explicit
Sub ShowDateSelection(control As IRibbonControl)
 Dim strMsg As String, strTitle As String
 
 Application.Calculate
 
 strMsg = HoursLost()
 strTitle = "The Future Date/Time Will Be:"
 
 MsgBox strMsg, vbOKOnly, strTitle
 
End Sub

Any advise on how to get around this? PS - I use this in a custom ribbon hence the ribbon part.

Upvotes: 1

Views: 151

Answers (1)

Brian M Stafford
Brian M Stafford

Reputation: 8868

One way to go about this is with the DateAdd function. Replace the following:

HoursLost = Now + Day + Hour + Minute

with something like this:

HoursLost = DateAdd("d", Day, Now)
HoursLost = DateAdd("h", Hour, HoursLost)
HoursLost = DateAdd("n", Minute, HoursLost)

Upvotes: 1

Related Questions