Subtracting time to produce time in previous day.

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
173
Office Version
365
Platform
Windows
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,362
Office Version
365
Platform
Windows
This works
- adapt to your own situation
Excel 2016 (Windows) 32 bit
A
B
C
D
E
1
TimeHoursminutesresult 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:confused:
- 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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,951
Office Version
2010
Platform
Windows
Below is for US date system. One hour and 15 mins is (1.25 hours/24 hours/day) days.
Excel Workbook
AB
18/10/2019 1:088/9/2019 23:53
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,102,630
Messages
5,488,000
Members
407,617
Latest member
Samanthad2007

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top