CODEWITHSUNDEEP
vbaexceldatevb6
Glenn G
Glenn G

Reputation: 667

Subtract a specified number of hours from a given date/time to get a new date/time

I'm trying to figure out how to calculate a date/time by subtracting a given number of hours from a given due date/time, so that I know when I need to start a process step on a product component in order to have it finished in time to ship to the customer.

For example, the product needs to be ready to ship to the customer by 15:00 on September 15, 2017

The final assembly and verification steps, from last to first, are

working backwards

I've spent the day searching for an example that would be close enough to what I want and I think I will have to do something with the DateTime function, I'm just not sure what at the moment

Upvotes: 1

Views: 12260

Answers (3)

Marc
Marc

Reputation: 993

The DateAdd method is used to add to or subtract from a Date object. The first parameter is the interval type. In the code below "h" is for hours and "n" is for minutes. To work backwards, enter your hours and minutes as negative values.

Dim dtShipTime As Date
Dim dtStep15Start As Date
Dim dtStep10Start As Date
Dim dtStep05Start As Date

dtShipTime = #9/15/2017 3:00:00 PM#

dtStep15Start = DateAdd("h", -12, dtShipTime)
dtStep15Start = DateAdd("n", -36, dtStep15Start)

dtStep10Start = DateAdd("h", -32, dtStep15Start)
dtStep10Start = DateAdd("n", -6, dtStep10Start)

dtStep05Start = DateAdd("h", -25, dtStep10Start)
dtStep05Start = DateAdd("n", -54, dtStep05Start)

Reference http://www.chennaiiq.com/developers/reference/visual_basic/functions/dateadd.asp

Upvotes: 0

A.S.H
A.S.H

Reputation: 29352

You can subtract n hours from a date by simply subtracting n/24 from it. You can also use the TimeSerial function to subtract hours, minutes and seconds. This example illustrates the two methods, it subtracts 1 hour and a half from the current time using the two methods.

Sub substractDates()
  Dim d1 As Date, d2 As Date, d3 as Date
  d1 = Now()
  d2 = d1 - TimeSerial(1, 30, 0)
  d3 = d1 - 1.5 / 24
  Debug.Print d1, d2, d3
End Sub

p.s. yet a third way is to use TimeValue("1:30:0") which is equivalent to TimeSerial(1, 30, 0).

Upvotes: 3

TJYen
TJYen

Reputation: 373

This macro will give you an Idea how to set it up: It can further processed to use user input or take time from a list.

Sub test()
Dim dtmstart As Date
Dim steps(2) As Double
Dim duration, hours As Double
dtmstart = "2017/9/15 15:00"
steps(0) = 12.6
steps(1) = 32.1
steps(2) = 25.9

MsgBox "Last step will need to be completed by: " & dtmstart
For Each c In steps
'convert hours to days
hours = c / 24
duration = dtmstart - hours
dtmstart = Application.Text(duration, "yyyy-mm-dd hh:mm")
MsgBox dtmstart
Next c
End Sub

Upvotes: 0

Related Questions