Excel people. I need help subtracting time in excel!!!?

Question

I have a cell that has the date and time listed and I need to have a cell below automatically subtract 12 hours from the date and time. The specifics for this example are A1=3/8/2007 11:40AM
B2= A1 - 12 hours.

Please help me if you can!

Answer 1

One option is to use fractional days... e.g. if you want to subtract 12 hours then use "=A1-0.5" and it will subtract half a day or 12 hours.

You can also specifically designate time by formating it as "hh:mm:ss"

Answer 2

Subtract 0.5 from the date & time.

In reality, behind the scenes, Excel stores dates by their serial number. The serial number is the number of days from 1/1/1900 until today. On the right hand side of the decimal point of the serial numbers is where Excel calculates the time.

Subtracting 1 will calculate the previous day. Subtracting .5 will calculate the previous 12 hours.

Formatting the serial number as a date (Format, cells, number) will convert the serial number into a more familiar form even though the 'real' vale in the cell is a decimal equivalent.

Hope this help.

Answer 3

I recommend adding a cell in which you put the hours you want to subtract:

A1 - enter the date and time formatted as you have in above.
B1 - format the cell as Time and in the Type box, select 37:30:55. Now enter 12 hours as: 12:00
C1 - format the same as A1, and enter the formula
=A1-B1

Now C1 should display: 3/7/2007 11:40 PM

Answer 4

use your time in "00:00:00"

dont forget the "