Share via

Add or Subtract Hours to Date/Time Field

Anonymous
2010-09-02T19:29:49+00:00

I have some date-time fields to which I need to add or subtract 4, 5 or 6 hours, depending on situation.  Can I do this without first parsing the entire field into two, calculating hours, then bringing them back together?

Example:

07/19/10 23:47

Need to add 5 hours to achieve result of :

7/20/10 04:47

Greatly appreciate any help on this!

Rita Brasher

FedEx Express

***************************** Missing the Newsgroups, but will learn to adapt!! *****************************

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2010-09-02T19:41:04+00:00

    Hi,

    To add 5 hours to a1

    =A1+TIME(5,0,0)

    or to subtract

    =A1-TIME(5,0,0)

    The date will adjust automatically if it rolls over midnight

    If this post answers your question, please mark it as the Answer.

    Mike H

    80+ people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-07-01T11:15:56+00:00

    profgold wrote:

    The "minus" hours is not working for me in Excel 2010. The formula of  =C4-TIME(3,0,0) returns a string of ###.  The Plus sign works fine, but I also need to subtract hours.

    We need to adjust for Pacific (+3) and  GMT (-5) based on the eastern zone time enrty in cell C4. Any thoughts?

    It appears that C4 contains only time, not date and time.  In that case, the following form can be used to avoid negative time:

    =C4-TIME(h,0,0)+(TIME(h,0,0) > C4)

    formatted as Time.  Substitute the earlier timezone offset for "h" (e.g. 5 to represent -5).

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2012-07-04T10:06:03+00:00

    Hi I would need something slightly different.

    I have to columns, one for date (e.g., 05/07/2012) and one for time (e.g., 20:00). I need a sepparate row to show that date+time combination minus 60 hours, so this would be 03/07/2012 08:00:00 and to be highlighted or conditionally formated in, lets say, red, when the given date & time minus 60 hours is reached.

    Can you help?

    Best regards,

    Gonçalo Couto

    0 comments
  3. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2012-07-01T11:02:16+00:00

    Hi,

    What exact entry do you have in cell C4?

    0 comments
  4. Anonymous
    2012-06-29T14:28:22+00:00

    Hi,

    To add 5 hours to a1

    =A1+TIME(5,0,0)

    or to subtract

    =A1-TIME(5,0,0)

    The date will adjust automatically if it rolls over midnight

    If this post answers your question, please mark it as the Answer.

    Mike H

    The "minus" hours is not working for me in Excel 2010. The formula of  =C4-TIME(3,0,0) returns a string of ###.  The Plus sign works fine, but I also need to subtract hours.

    We need to adjust for Pacific (+3) and  GMT (-5) based on the eastern zone time enrty in cell C4. Any thoughts?

    Thanks!

    0 comments