Date formats and computations -- Help

S

sonarmark

I need to apply functions and formulas to dates in the "yy mm dd" format. Why
was this format not included as a date?

I need for example to subtract date b from date a and return a day for day
result in the format above.

Then I need to be able to add dates in this format. While I can input
numbers in the format above they will not add/subtract like dates ie at 30
they add 1 month to the months.

I did notice that Open Office will format in this way but I prefer Excel.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
 
F

Fred Smith

In Excel, formatting has nothing to do with date arithmetic. The arithmetic
is the same regardless of the format you choose.

Excel stores all dates as the number of days since Jan 1, 1900. You can see
this if you format the cell as General -- you'll see a number like 38,195 --
the number of days which have elapsed since the turn of the last century.

One of the quirks of Excel is that inputting data is not controlled by the
cell's format. For a date, it's controlled by the Windows Regional Settings.

As long as you have a true Excel date in the cell (and not text masquerading
as a date), you can perform all functions on these dates, and display the
results in any format you choose.

Now that the lesson is over, all you have to do is tell us what you mean by
"subtract date b from date a and return a day for day result in the format
above". An example would be very helpful.

To subtract dates, it's as simply as:
=b1-a1

However, if the result, is, say, 32 days, how would you expect that to be
displayed in "yy mm dd" format? My guess is you don't want yy mm dd
format -- you want General or a Number format.

Regards,
Fred.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top