CODEWITHSUNDEEP
excelvbadatetime
toofly
toofly

Reputation: 2307

Working with dates in Visual Basic / Excel

Very new to working with Visual Basic / Excel. I am trying to write a quick script that enters the current time in one column, and allows the user to enter how many days/hours/minutes will pass until a new time, and output that in another column.

I'm sure this isn't the best way to do it, but what I have so far is the following. I have given up on fiddling with dates, and am just working with the time:

Sub TimeModule()
Dim DaysLeft, HoursLeft, MinutesLeft As Double
DaysLeft = Val(InputBox("Days left"))
HoursLeft = Val(InputBox("Hours left"))
MinutesLeft = Val(InputBox("Minutes left"))

Dim CurrentTime As Date
CurrentTime = TimeValue(Now())

ActiveCell.Value = CurrentTime
ActiveCell.Offset(0, 1) = CurrentTime + Time(HoursLeft, MinutesLeft, 0)

End Sub

I am getting an error, of course. If anyone could shed some light on a better way to do this, along with the functions I'm misusing, I would really appreciate it!

Edit: I would, of course ultimately like for the script to handle days as well.

Upvotes: 0

Views: 4202

Answers (3)

Dick Kusleika
Dick Kusleika

Reputation: 33145

When you 'Dim' in that fashion, you have to record the data type for each variable. The way you have it MinutesLeft is a Double and everything is (by default) a Variant.

The Time function you're looking for is TimeSerial.

Dates are stored as the number of days since a certain date. To add days to a date, you can simply add the numbers together.

Sub TimeModule()

    Dim lDaysLeft As Long
    Dim lHoursLeft As Long
    Dim lMinutesLeft As Double
    Dim dtCurrent As Date

    lDaysLeft = Val(InputBox("Days left"))
    lHoursLeft = Val(InputBox("Hours left"))
    lMinutesLeft = Val(InputBox("Minutes left"))

    dtCurrent = Now()

    ActiveCell.Value = dtCurrent
    ActiveCell.Offset(0, 1).Value = dtCurrent + lDaysLeft + TimeSerial(lHoursLeft, lMinutesLeft, 0)

End Sub

Upvotes: 0

Iakovosian
Iakovosian

Reputation: 361

I think this is possible just using cell functions in Excel, if I've understood you correctly.

For example, this is what you'd see...

Time Now:           Days:   Hours:  Minutes:    New Time:
30/05/2012 23:34    15      6       23          15/06/2012 05:57

...and this is what is in each cell (assuming top-left cell is A1)...

Time Now:   Days:   Hours:  Minutes:   New Time:
=NOW()      15      6       23         =A2+B2+TIME(C2,D2,0)

Describing each function:

  • NOW() returns the current date and time formatted as a date and time.
  • DATE(year,month,day) returns the number that represents the date in MS Excel date-time code.
  • TIME(hours,minutes,seconds) converts hours, minutes, and seconds given as numbers to an Excel serial number, formatted with a time format.

Dissecting the equation in the last cell:

  • A2 is the cell containing the current date/time (as of last worksheet calculation).
  • B2 is the user-inputted value for days.
  • TIME(C2,D2,0) is the TIME() function, taking the user-inputted values for hours and minutes from cells C2 and D2 respectively.

Is this anything like your intended functionality...?

Upvotes: 3

Gutti
Gutti

Reputation: 657

If you want to use VBA the only issue with your code is the "Time" function. You can use CDate instead :

Sub TimeModule()
  Dim DaysLeft, HoursLeft, MinutesLeft As Double
  DaysLeft = Val(InputBox("Days left"))
  HoursLeft = Val(InputBox("Hours left"))
  MinutesLeft = Val(InputBox("Minutes left"))

  Dim CurrentTime As Date
  CurrentTime = TimeValue(Now())

  ActiveCell.Value = Now()
  ActiveCell.Offset(0, 1) = ActiveCell.Value + DaysLeft + CDate(HoursLeft & ":" & MinutesLeft)
  'ActiveCell.Offset(0, 1) = CurrentTime + Time(HoursLeft, MinutesLeft, 0)

End Sub

Upvotes: 0

Related Questions