Date function in ACCRINTM requires date format not available
 

The date entry in the ACCRINTM formula requires the date to be in as
year,month,day ie 2007,10,13 for 13th October 2007 but there is no option to
save dates in that format when formatting the cells as DATE or CUSTOM. This
makes it very tedious to have to amend each date from a column of dates when
I should be able to paste the cell containing the date into this formule. How
do I achive this?
Thanks for your help.

Date function in ACCRINTM requires date format not available
 

Hi Pev,

I think that you have misinterpreted the help.

Dates should be entered by using the DATE function, OR AS RESULTS OF OTHER
FORMULAS OR FUNCTIONS. For example, use DATE(2008,5,23) for the 23rd day of
May, 2008. Problems can occur if dates are entered as text.

The example uses references to cells with dates.

Regards,

OssieMac

"Pev" wrote:

The date entry in the ACCRINTM formula requires the date to be in as
year,month,day ie 2007,10,13 for 13th October 2007 but there is no option to
save dates in that format when formatting the cells as DATE or CUSTOM. This
makes it very tedious to have to amend each date from a column of dates when
I should be able to paste the cell containing the date into this formule. How
do I achive this?
Thanks for your help.

Date function in ACCRINTM requires date format not available
 

Hi Thanks for your response. I am not entering the dates as text but in a
date format that is one of the Date options when formatting a cell as a date.
It would seem strange not to be able to insert a date from a column of dates
into this function. I would have expected to be able to change the DATE
format in my column of dates to the format required to be entered into this
function. I take it that this is not possible?
Regards

Pev Walsh

"OssieMac" wrote:

Hi Pev,

I think that you have misinterpreted the help.

Dates should be entered by using the DATE function, OR AS RESULTS OF OTHER
FORMULAS OR FUNCTIONS. For example, use DATE(2008,5,23) for the 23rd day of
May, 2008. Problems can occur if dates are entered as text.

The example uses references to cells with dates.

Regards,

OssieMac

"Pev" wrote:

The date entry in the ACCRINTM formula requires the date to be in as
year,month,day ie 2007,10,13 for 13th October 2007 but there is no option to
save dates in that format when formatting the cells as DATE or CUSTOM. This
makes it very tedious to have to amend each date from a column of dates when
I should be able to paste the cell containing the date into this formule. How
do I achive this?
Thanks for your help.

Date function in ACCRINTM requires date format not available
 

You don't have to change the date format. Just use the cell reference of the
date just so long as the cell contains a valid date in any format you like.
Date is only a serial number starting from 1900 and the way it is displayed
does not affect the formula.

Example:-
formula is ACCRINTM(issue,settlement,rate,par,basis)

enter the formula as this:-
=ACCRINTM(A2,A3,A4,A5,A6)
where
A2 is the issue date (in any valid date format you like)
A3 is settlement date (in any valid date format you like)
A4 is rate
A5 is par
A6 is basis

Just as a little extra, If you enter the following formula into a cell then
it returns a date in the cell in whatever format you want to set the cell to.
It is a date value. The only time you have to use this method in the above
formula is if you don't already have the date in a cell otherwise simply
reference the cell with the date.

=DATE(2008,5,23)

Regards,

OssieMac




"Pev" wrote:

Hi Thanks for your response. I am not entering the dates as text but in a
date format that is one of the Date options when formatting a cell as a date.
It would seem strange not to be able to insert a date from a column of dates
into this function. I would have expected to be able to change the DATE
format in my column of dates to the format required to be entered into this
function. I take it that this is not possible?
Regards

Pev Walsh

"OssieMac" wrote:

Hi Pev,

I think that you have misinterpreted the help.

Dates should be entered by using the DATE function, OR AS RESULTS OF OTHER
FORMULAS OR FUNCTIONS. For example, use DATE(2008,5,23) for the 23rd day of
May, 2008. Problems can occur if dates are entered as text.

The example uses references to cells with dates.

Regards,

OssieMac

"Pev" wrote:

The date entry in the ACCRINTM formula requires the date to be in as
year,month,day ie 2007,10,13 for 13th October 2007 but there is no option to
save dates in that format when formatting the cells as DATE or CUSTOM. This
makes it very tedious to have to amend each date from a column of dates when
I should be able to paste the cell containing the date into this formule. How
do I achive this?
Thanks for your help.

Date function in ACCRINTM requires date format not available
 

Thanks for your help. I'll try that out later today. Much appreciated.

Pev

"OssieMac" wrote:

You don't have to change the date format. Just use the cell reference of the
date just so long as the cell contains a valid date in any format you like.
Date is only a serial number starting from 1900 and the way it is displayed
does not affect the formula.

Example:-
formula is ACCRINTM(issue,settlement,rate,par,basis)

enter the formula as this:-
=ACCRINTM(A2,A3,A4,A5,A6)
where
A2 is the issue date (in any valid date format you like)
A3 is settlement date (in any valid date format you like)
A4 is rate
A5 is par
A6 is basis

Just as a little extra, If you enter the following formula into a cell then
it returns a date in the cell in whatever format you want to set the cell to.
It is a date value. The only time you have to use this method in the above
formula is if you don't already have the date in a cell otherwise simply
reference the cell with the date.

=DATE(2008,5,23)

Regards,

OssieMac




"Pev" wrote:

Hi Thanks for your response. I am not entering the dates as text but in a
date format that is one of the Date options when formatting a cell as a date.
It would seem strange not to be able to insert a date from a column of dates
into this function. I would have expected to be able to change the DATE
format in my column of dates to the format required to be entered into this
function. I take it that this is not possible?
Regards

Pev Walsh

"OssieMac" wrote:

Hi Pev,

I think that you have misinterpreted the help.

Dates should be entered by using the DATE function, OR AS RESULTS OF OTHER
FORMULAS OR FUNCTIONS. For example, use DATE(2008,5,23) for the 23rd day of
May, 2008. Problems can occur if dates are entered as text.

The example uses references to cells with dates.

Regards,

OssieMac

"Pev" wrote:

The date entry in the ACCRINTM formula requires the date to be in as
year,month,day ie 2007,10,13 for 13th October 2007 but there is no option to
save dates in that format when formatting the cells as DATE or CUSTOM. This
makes it very tedious to have to amend each date from a column of dates when
I should be able to paste the cell containing the date into this formule. How
do I achive this?
Thanks for your help.