Results 1 to 3 of 3

Thread: Subtracting time to produce time in previous day.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2006
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Subtracting time to produce time in previous day.

    Hi

    I would like to be able to subtract a combination of hours, minutes and seconds from a date and time.

    The resultant time may be in the previous day and that is where the problem is.

    For example :

    10/08/2019 01:08:00

    I would like to be able to subtract 1 hour and 15 minutes from this time which should result in the following:

    09/08/2019 23:53:00 a time in the previous day.

    Does anybody know how to do this?

    I would like to represent the time elements to subtract as negative numbers and add them to the time so that I can, using the above example, add minus 1 hour and minus 15 minutes to 10/08/2019 01:08:00.

    Thanks

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Subtracting time to produce time in previous day.

    This works
    - adapt to your own situation
    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    1
    Time Hours minutes result formula in D2
    2
    10/08/2019 01:08
    1
    15
    09/08/2019 23:53
    =A2-(B2+C2/60)/24
    Sheet: Sheet1


    I would like to represent the time elements to subtract as negative numbers and add them to the time so that I can, using the above example, add minus 1 hour and minus 15 minutes to 10/08/2019 01:08:00.
    - unsure what this means
    - if you cannot figure out what to do based on above example, then post a table of required results including both positive and negative examples

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,491
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Subtracting time to produce time in previous day.

    Below is for US date system. One hour and 15 mins is (1.25 hours/24 hours/day) days.
    Sheet1

    AB
    18/10/2019 1:088/9/2019 23:53

    Spreadsheet Formulas
    CellFormula
    B1=A1-1.25/24


    Excel tables to the web >> Excel Jeanie HTML 4
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •