Macro - subtracting times

**dobbinuk** · 08-16-2011, 04:10 AM

Hello. I guess this question has been asked many times before but I have done so many searches to try to work this out:

Using a macro, I wish to take a date/time from within a cell, subtract or add hours and return it as a time (usually to convert between AP/US/BST) time zones. I will assign the macro to a button.
The date/time value is general format: "08/08/2011 14:17:15"

Using the worksheet function "=A4 - TIME(5,,)" (for example) returns "9:17AM", which is perfect and exactly what I wanted but i cannot figure out how to produce the same result within a macro.

I have tried using...

Range("A4").Select
    Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
    ActiveCell.Value = ActiveCell.Value - <a number of time functions>
    ActiveCell.Offset(1, 0).Select
    Loop

... but I must be using the wrong syntax or perhaps I need to format cells first.

Could anyone guide me to correct the code above to subtract 5 hours from the time in the ActiveCell being processed please?

Regards,
Dobbs.

**dobbinuk** · 08-17-2011, 04:02 AM

I have uploaded a sample spreadsheet, which hopefully explains what I want to achieve. Any help gratefully received.

Regards,
Dobbs.

**TMShucks** · 08-17-2011, 04:16 AM

Welcome to the forum.

Please take a few moments to read the forum rules and add CODE tags to your code sample.

Regards

**kvsrinivasamurthy** · 08-17-2011, 05:46 AM

Pl use thefollowing code.Assumed a4 contains date & time.
m1 subtracts two days from A4 value.
m2 subtracts 1 Hr 2Mts and 3 Secs from A4 value.
n gives the required format.

Sub datetime()
Dim k As Date
k = Range("a4").Value
m1 = ActiveCell.Value - 2
m2 = ActiveCell.Value - TimeSerial(1, 2, 3)

n = Format(m1, "mm/dd/yyyy hh:mm:ss")
End Sub

**arthurbr** · 08-17-2011, 06:19 AM

Kvsrinivasamurthy

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc

**dobbinuk** · 08-18-2011, 07:27 AM

Added code tags as requested and also example sheet.

**dobbinuk** · 08-18-2011, 01:16 PM

kvsrinivasamurthy, Thanks for the response. Unfortunately I get the error message "Run-time error '13': Type mismatch when I try to run the code that you supplied.