CODEWITHSUNDEEP
vbadate
Selvam
Selvam

Reputation: 1093

Excel modify date & time

In the VBA script, I need to modify the date & time. i.e. Increment or decrement the day, set time to 8am or 8 pm depending on the conditions.

I have here the relevant code segment -

Variable declared -

' To reference the cell having the source date 
Dim d As String 

' To reference the cell where the modified date is written
Dim destCell As String

' Some cell that contains 1 or 0
Dim somecell As String

'If condition and value assignment

If Range(somecell).Value = 1 Then
    Range(destCell).Value = DATE(YEAR(Range(d)),MONTH(Range(d)),DAY(Range(d)-1))+TIME(8,0,0)

Question: What I have done is to decrement the day and set the time to 8am, when the condition is satisfied. I get a syntax error. Please help.

Upvotes: 1

Views: 2863

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

You need to replace DATE with DateSerial and TIME with TimeSerial:

Dim datSource As Date
datSource = Range(d).Value
If Range(somecell).Value = 1 Then
    Range(destCell).Value = _
        DateSerial(Year(datSource), Month(datSource), Day(datSource) - 1) + _
        TimeSerial(8, 0, 0)

Upvotes: 3

Related Questions