When subtracting dates issue
 

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

When subtracting dates issue
 

On Thu, 6 Mar 2008 21:05:00 -0800, Adnan (donotspam)
wrote:

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

When subtracting dates issue
 

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" (donotspam) wrote in message
...
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

When subtracting dates issue
 

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

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


"Ron Rosenfeld" wrote:

On Thu, 6 Mar 2008 21:05:00 -0800, Adnan (donotspam)
wrote:

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

When subtracting dates issue
 

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

"Adnan" (donotspam) wrote in message
...
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.

"Ron Rosenfeld" wrote:

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

On Thu, 6 Mar 2008 21:05:00 -0800, Adnan
(donotspam)
wrote:

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

When subtracting dates issue
 

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

"Tyro" wrote:

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" (donotspam) wrote in message
...
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

When subtracting dates issue
 

On Thu, 6 Mar 2008 23:07:00 -0800, Adnan (donotspam)
wrote:

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

When subtracting dates issue
 

On Fri, 7 Mar 2008 02:19:04 -0800, Adnan (donotspam)
wrote:

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

When subtracting dates issue
 

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


"Ron Rosenfeld" wrote:

On Fri, 7 Mar 2008 02:19:04 -0800, Adnan (donotspam)
wrote:

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


Widen your column
--ron

When subtracting dates issue
 

On Fri, 7 Mar 2008 19:35:00 -0800, Adnan (donotspam)
wrote:

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

When subtracting dates issue
 

Ron,

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

Again, thank you!
Adnan



"Ron Rosenfeld" wrote:

On Fri, 7 Mar 2008 19:35:00 -0800, Adnan (donotspam)
wrote:

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

When subtracting dates issue
 

On Fri, 7 Mar 2008 23:14:00 -0800, Adnan (donotspam)
wrote:

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