Prev: Custom footer date format
Next: Add blank columns to long list of data - Excel 2007
From: deepak on 8 Sep 2009 08:32
Hello !

Thanks to all you for helping out me in my previous problem. This i have a
problem with diplaying negative times,

Say A1= 10:30 (format is hh:mm) and B1 = 8:30 (format is hh:mm).
when C1=B1-A1, the value is displayed as ##### with notification negative
dates and times are displayed as ######.
Is there a way to display the negative time (for e.g. 8:30 - 10:30 = -2:00)

Thanks

From: Gary''s Student on 8 Sep 2009 09:09
In C1 enter:
=IF(B1<A1,"-","") & TEXT(ABS(B1-A1),"hh:mm")

--
Gary''s Student - gsnu200902


"deepak" wrote:

> Hello !
>
> Thanks to all you for helping out me in my previous problem. This i have a
> problem with diplaying negative times,
>
> Say A1= 10:30 (format is hh:mm) and B1 = 8:30 (format is hh:mm).
> when C1=B1-A1, the value is displayed as ##### with notification negative
> dates and times are displayed as ######.
> Is there a way to display the negative time (for e.g. 8:30 - 10:30 = -2:00)
>
> Thanks
>
From: Dave Peterson on 8 Sep 2009 11:58
If you switch to the 1904 date system:
Tools|Options|calculation tab (in xl2003 menus)

You can see negative times and dates.

Be aware that this setting is a workbook setting and can cause trouble when
copying dates from a different workbook.

And your existing dates in this workbook will now be 4 years and 1 day larger.

Saved from a previous post:

One way to add (or subtract) those four years back is to find an empty cell, put
1462 into that cell.

Copy that cell.

Select your range that contains the dates. Edit|PasteSpecial|click Add (or
subtract) (in theoperation box) and check values.

You may want to do it against a copy...just in case.

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.



deepak wrote:
>
> Hello !
>
> Thanks to all you for helping out me in my previous problem. This i have a
> problem with diplaying negative times,
>
> Say A1= 10:30 (format is hh:mm) and B1 = 8:30 (format is hh:mm).
> when C1=B1-A1, the value is displayed as ##### with notification negative
> dates and times are displayed as ######.
> Is there a way to display the negative time (for e.g. 8:30 - 10:30 = -2:00)
>
> Thanks

--

Dave Peterson
 | 
Pages: 1
Prev: Custom footer date format
Next: Add blank columns to long list of data - Excel 2007