CODEWITHSUNDEEP
excel
user2236569
user2236569

Reputation: 1

How To Excel Date/Time Subtraction

I am trying to set a formula in Excel that would subtract days from 4/29/2013 16:00, but always keep the time at 6:00:00 PM. I found this formula:

=DATE(YEAR(B2),MONTH(B2),DAY(B2)-3)

to subtract the days, but it returns 4/26/2013 00:00. I want the time (00:00) to always be 18:00. Can someone tell me how I can accomplish this?

Upvotes: 0

Views: 3487

Answers (2)

David Zemens
David Zemens

Reputation: 53623

In VBA you can use the DateAdd function:

Sub DateChange()
Dim originalDate as Date
Dim newDate as Date

originalDate = "4/29/2013 16:00"
newDate = DateAdd("d", -3, originalDate)

MsgBox newDate,vbInformation

End Sub

Or, you could write a custom function to approximate the DateAdd function and make it available on the sheet:

Public Function wsDateAdd(interval As String, number As Double, myDate As Date)
'function to make 'DateAdd' VBA function available on worksheet.'

Dim newDate As Date

Select Case LCase(interval)
    Case "y", "m", "d", "h", "n", "s" 'years-months-days-hours-minutes-seconds'
        newDate = DateAdd(interval, number, myDate)
    End Select

wsDateAdd = newDate
End Function

Before:

Formula...

After:

unformatted value

Then apply the appropriate number format to the cell, and voila:

formatted date

Upvotes: 0

Zhenya
Zhenya

Reputation: 174

The easiest way for you would be to add the TimeValue("18:00") or 0.75 (18/24) to your formula. Subtracting 3 days may be also an arithmetic operation.

=TRUNC(NOW())-3 + 0.75

Upvotes: 1

Related Questions