Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / July 2006

Tip: Looking for answers? Try searching our database.

date subtraction difficulty

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Peter - 18 Jul 2006 17:58 GMT
I'm trying to subtract dates, to calculate time duration.  I'm
subtracting column A from column B into column C.  Column C is
formatted 'm "mos", dd "days"' and columns A and B are formatted
'mm/dd/yy/@'  Unfortunately each of the durations is 1 month too
long.  That is, if the real duration is 4 days, column C will
display '1 mos, 4 days'

Any clue what I'm doing here?

Thanks much.
Ron Rosenfeld - 18 Jul 2006 18:59 GMT
>I'm trying to subtract dates, to calculate time duration.  I'm
>subtracting column A from column B into column C.  Column C is
[quoted text clipped - 6 lines]
>
>Thanks much.

If you are formatting the target cell as a date, Excel will display it as a
date!

So if B1-A1 = 4, Excel interprets that as a date, doing just what you told it
to do.

Day 4 in Excel is 4 January 1900

Since the "m" in your format code means to display the numeric equivalent of
the month of the year, and since the month of the year is January, Excel will
display a "1" for your "m" token.

The difference between two dates is NOT a date; rather it is a number.

So for the number of Days, you could format as       0" days"

For days and months, you will need to decide what you mean by "month" since a
month can be 28, 29, 30 or 31 days long.

--ron

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.