how to get negative time by substracting one day from previous date and time in excel 2007

Hi,
By getting to excel options and changing the day format to 1904, and then subtracting the present day from previous day does not give the result in the same format.
For example, I am enclosing a worksheet and would request the solution. I want the result in day hours time format, but the result is showing something not acceptable format.
Please note that i have changed 1904 for this enclosed worksheet,in my excel option. I also assure you that i have not linked any document with this date format.

Hi jajuj,

The answer you are getting is correct. The difference between the two times is 9.5 hours. (It has nothing to do with the time format you are using. If you remove formatting, you are saying 40136.79167-40134.6875 = .895833).

What did you expect to see? What answer did you want? Maybe you have to format it as a number and multiply by 24.

Regards,

David


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

-1/1/04 9:30 PM how you say that it is 9.5 hours. could i get in day and time like day hr min. What does that -1/1/04 9:30 Pm denotes. Will not be misleading if i see this result.

You didn't say what the answer should be! What did you expect? (Remember that there really is no such thing a negative time!)

When you minus a number, you get correct answer.
I want the time to be day hr mm format
As time is a determinant for day. I have come across some problems, where these kind of needed. could it be possible.
This negative time problem often occurs when calculating an elapsed time - for example, calculating the number of hours worked given a start time and an end time. This presents no problem if the two times fall in the same day. But if the work shift spans midnight, the result is an negative time.

What do you want the answer to be? You say -1/1/04 9:30 PM is wrong. What is correct!

What is -1/1/04 in the answer denote

You have the cell formatted as a date, therefore the answer is coming back at the 1st day of the year 1904 (because that is the way you have set the workbook). Have a look at post #2 - I explained what was happening there. The answer you are getting is 0.895833 which when formatted as a date is 1/1/04 9:30 PM.

I ask again, what answer do you expect. I can't help if I don't know what you want.


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi, Thank you very much. Now i understand after your last reply. I thought, there is some error in 1/1/04. It is the format i set to get the time. Thank you. I thought, that it is minus the dates in question. Thank you.