When subtracting dates issue

[Adnan]

I have a start date in A1 formatted as dd/mm/yyyy hh:mm
I have another date (finish date) in cell B1 formatted the same (dd/mm/yyyy
hh:mm).
I have a formula in C1 that subtract A1-B1, cell is formatted as dd:hh:mm
It works if results are positive, if negative I get an error, lots of pond
signs within the cell (#########)

Any tip/help is much appreciated.
Adnan

 

[Ron Rosenfeld]

I have a start date in A1 formatted as dd/mm/yyyy hh:mm
I have another date (finish date) in cell B1 formatted the same (dd/mm/yyyy
hh:mm).
I have a formula in C1 that subtract A1-B1, cell is formatted as dd:hh:mm
It works if results are positive, if negative I get an error, lots of pond
signs within the cell (#########)

Any tip/help is much appreciated.
Adnan

Unless you switch to the 1904 date system, you will not be able to display
negative date/times using date formatting
--ron

 

[Tyro]

You have a start date in A1. You have a finish date in B1. If your finish
date is equal to or greater than your start date, then the formula is =B1-A1
not A1-B1

Tyro

 

[Adnan]

Thanks for your response Ron, how do I implement that?

Also, I’ve seen something close to what I need with Dadeif function but
cannot implement.

 

[David Biddulph]

Did you try typing the phrase "1904 date system" into Excel help?

 

[Adnan]

Tried 1904, no luck --- I wonder why wouldn’t it be possible to display
negative results in excel. Is there any way around it?

 

[Ron Rosenfeld]

Thanks for your response Ron, how do I implement that?

Also, I’ve seen something close to what I need with Dadeif function but
cannot implement.

Tools/Options/Calculations
Workbook Options Select 1904 Date System

I see in another of your messages that you still see the #####
That is because Excel (helpfully?) formatted your result as a date, and it's
too wide for the cell. If you widen the cell, and reformat to your dd:hh:mm
that you want, that should take care of that problem.

Note that if you change to the 1904 Date, any previously entered dates will be
increased by four years + 1 day.

One other item -- I don't know how many days difference you are doing, but the
dd formatting will not display more than 31 days.

If you need to display more than 31 days, that will be the integer part of your
result and, if you format it as "number" or General, will display positive or
negative. You can then display the fractional part separately using the hh:mm
formatting. (Split these using the INT and MOD functions)

--ron

 

[Ron Rosenfeld]

Tried 1904, no luck --- I wonder why wouldn’t it be possible to display
negative results in excel. Is there any way around it?

Widen your column
--ron

 

[Adnan]

I can not seem to implement anything. Can anyone out there be more
detailed/post/email a sample or anything, please?

 

[Ron Rosenfeld]

I can not seem to implement anything. Can anyone out there be more
detailed/post/email a sample or anything, please?

This assumes that there is not more than 31 days difference between the two
dates. I asked you about that before, but you have not responded.

On the main menu bar, select Tools/Options. On the Calculation Tab, select
1904 date system (towards the bottom left under workbook options).

Make the following entries:

A1: 1/6/03 6:15 AM
B1: 1/15/03 8:00 AM
C1: =A1-B1

With C1 selected, select Format/Cells
Select the Number tab
Select Custom
Type: dd:hh:mm

If you only see ### in C1, widen the column
With C1 selected, from the top menu select Format/Column/AutoFit
Selection. C1 should display -10:01:45


--ron

 

[Adnan]

Ron,

This is sweet answer/demonstration. Thank you so much.
I got if finally with you great help!

Again, thank you!
Adnan

 

[Ron Rosenfeld]

Ron,

This is sweet answer/demonstration. Thank you so much.
I got if finally with you great help!

Again, thank you!
Adnan

You're welcome. Thanks for the feedback.
--ron