| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert date + time text format to date format | Excel Worksheet Functions | |||
| date format and the RIGHT function | Excel Worksheet Functions | |||
| date format and the RIGHT function | Excel Worksheet Functions | |||
| date format and the RIGHT function | Excel Worksheet Functions | |||
| Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) | |||
Hybrid Mode
