date() function error

[dhm3159]

In office pro 2003, excel the dates between function mis calculates i.e.
creating a amortization for simple interest using the days() between
function mis-calculates the number of days. i.e. aug 10, 2006 to sept 5, 2006
is 27 days, date() function reports 25 days, causing interest and principle
to be wrong and ending balance to be incorrect.

 

[Niek Otten]

AFAIK there is no DAYS() function in Excel. You just subtract one date from the other to get the number of days between them.
Maybe you mean DAYS360() function? 360 Days calculations have their own set of rules. 25 Seems to be the correct answer for your
example.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| In office pro 2003, excel the dates between function mis calculates i.e.
| creating a amortization for simple interest using the days() between
| function mis-calculates the number of days. i.e. aug 10, 2006 to sept 5, 2006
| is 27 days, date() function reports 25 days, causing interest and principle
| to be wrong and ending balance to be incorrect.
|
|

 

[Joel]

this will give you the correct count

=DATE(YEAR(I1412),MONTH(I1412),DAY(I1412))-DATE(YEAR(H1412),MONTH(H1412),DAY(H1412))+1

the problem is the time of day is probably different in the two dates
causing different answers. My correction will strip of the hours and minutes
from the time.= getting you the reference to the same point in time.

I don't know if the answer should be 26 or 27. Is the difference beteen
today and tomorrow 1 or 2. Should you include from mightnight today until
midnight tomorrow, or are you including from noon today until noon tomorrow.

 

[JE McGimpsey]

Assuming that I1412 and H1412 both contain dates, that formula will
always return the same value as

= I1412 - H1412 + 1

 

[Joel]

I think the function does whatt the help menu says. The help on the
days360() function says the following

Returns the number of days between two dates based on a 360-day year (twelve
30-day months), which is used in some accounting calculations. Use this
function to help compute payments if your accounting system is based on
twelve 30-day months.


for the 3rd parameter = method.

FALSE or omitted U.S. (NASD) method. If the starting date is the 31st of a
month, it becomes equal to the 30th of the same month. If the ending date is
the 31st of a month and the starting date is earlier than the 30th of a
month, the ending date becomes equal to the 1st of the next month; otherwise
the ending date becomes equal to the 30th of the same month.
TRUE European method. Starting dates and ending dates that occur on the 31st
of a month become equal to the 30th of the same month.

 

[BobM]

AFAIK there is no DAYS() function in Excel. You just subtract one date from the other to get the number of days between them.
Maybe you mean DAYS360() function? 360 Days calculations have their own set of rules. 25 Seems to be the correct answer for your
example.

-- so are you saying this function does not count sat & sun?
Kind regards,

Niek Otten
Microsoft MVP - Excel

| In office pro 2003, excel the dates between function mis calculates i.e.
| creating a amortization for simple interest using the days() between
| function mis-calculates the number of days. i.e. aug 10, 2006 to sept 5, 2006
| is 27 days, date() function reports 25 days, causing interest and principle
| to be wrong and ending balance to be incorrect.
|
|

 

[JE McGimpsey]

No. DAYS360() assumes the year is divided into 12 months of 30 days, and
calculates accordingly.