excel - subtracting TIME

A1 cell has formatting [h]:mm (this is the sum of several other cells)

the value of A1 is开发者_运维百科 currently 64:00

i need B1 to be A1-40 hours. currently B1 formula is =A1-TIME(40,0,0)

the formatting on B1 is also h:mm

for some reason the value it is giving me is 0:00

does anyone know what i am doing wrong

The format [h]:mm does not represent a number of hours. It specifically represents a date and time. In your case, with an entry of 64 hours, this represents the date January 2, 1900 at 4 pm in the afternoon.

The formula TIME(40,0,0) returns the exact date of January 0, 1900 at 4 pm in the afternoon. (Yeah, I know, January 0 doesn't exist, but Excel thinks it does).

So when you subtract January 0, 1900 16:00 from January 2, 1900 16:00, you get the date, January 2, 1900 at midnight (00:00), which is exactly 48 hours after the point at which Excel begins to count time. So if you have that cell formatted [h]:mm, it will display 48:00.

EDIT

The reason the formula Time(40,0,0) returns Jan 0 1900, 16:00 is because the TIME functions throws out anything in the hour field that is a multiple of 24 hours, and only deals with the remainder, in this case 16 hours. You could enter 16, 40, 64, 88, etc in the hour field of the time function, and they would all return Jan 0 1900, 16:00.

When dealing with dates and times in Excel, remember that Excel deals in days; so a value of 1 is equivalent to 1 day. A value of 1.5 is equivalent to 1 day, 12 hours.

All of Excel's date and time functions deal with time as a specific date and time measured from a fixed starting point (January 0, 1900, midnight).

To deal with something like number of hours worked, for a timesheet or something similar, you need to not treat the number hours as times or dates, just treat them as integers or regular decimal numbers.

That's because Excel calculates first the value of the function Time(40,0,0), which is equal to 16:00:00 (40%24 = 16). So, it finally is 64:00:00 - 16:00:00 = 48:00:00. You should set another cell with the value of 40:00:00, and then subtract the values between both cells.