Trouble with Case - DateAdd function VBA

Question

Case with DateAdd function.

Here is my code

Select Case Range("O7").Value

    Case "CST"
         Sheet1.Cells(yz, 11).Value = DateAdd("hh", -6, Sheet1.Cells(yz, 11))
    Case "EST"
         Sheet1.Cells(yz, 11).Value = DateAdd("hh", -5, Sheet1.Cells(yz, 11))
    Case "MST"
         Sheet1.Cells(yz, 11).Value = DateAdd("hh", -7, Sheet1.Cells(yz, 11))
    Case "PST"
         Sheet1.Cells(yz, 11).Value = DateAdd("hh", -8, Sheet1.Cells(yz, 11))
    Case Else
         Range(Sheet1.Cells(yz, 11)).Value = Sheet1.Cells(yz, 11)

End Select

Trying maniputlate date that is already present and change hrs from their time zone to UTC. Thoughts on how to fix my code? "O7" houses list of CST,MST,EST, PST. YZ, 11 is the date in current time zone.

Try this: http://stackoverflow.com/questions/1600875/how-to-get-the-current-datetime-in-utc-from-an-excel-vba-macro — Jason Faulkner, Nov 25 '14 at 16:53

score 1 · Accepted Answer · answered Nov 25 '14 at 17:53

1

The proper syntax for DateAdd() is just one "h" to indicate hours. Also, your last line is redundant... Remove Range(Sheet1.Cells(yz, 11)).Value = Sheet1.Cells(yz, 11)

answered Nov 25 '14 at 17:53

Chrismas007

6,085
4
24
47

Trouble with Case - DateAdd function VBA

1 Answers1