Recently we received this query:

I need a little bit of information from you about ms excel function.

 

I would like to know whether can I set a date as repayment due date

between two dates in excel.

 

Suppose if we extend finance to a person between 06th day and 15 day

of a month, can I set the due date as 11th day of next month thru

excel formula.

 

Likewise if the loan is sanctioned between 16th day and 25th day, Can

I set the due date as 21st date

 

In the same manner if the loan is sanctioned between 26th day and 05th

day, Can I set the due date as 02nd date.

 

To set 3 dates between three sets of dates, we require conditional function.

 

IF you don’t mind, could you please inform me by using which formula,

we may set the above.

Our solution is that by using the mix of functions being DATE, EDATE and EOMONTH it would be possible to achieve this.  To see how to use these functions you can watch the video clips on

http://www.auditexcel.co.za/DATE_DAY_MONTH_YEAR.html and

http://www.auditexcel.co.za/EOMONTH_EDATE.html

Recently I have come across some very annoying problems in Excel 2007. In particular I am finding that with certain Excel functions, the code is actually lost after you perform a save or try and open up a file. The real problem is that is isn’t consistent. So sometimes it happens and some times not.

For example if I have a formula that says =EOMONTH(A1,1) and results in 31 Jan 2008, when I reopen it still shows 31 Jan 2008 but when you look at the formula it is =#n/a. Once you perform a calculation all other cells linked to this cell have the #n/a problem.

The problem seems to occur with the functions that were previously available through the Analysis toolpack. After re doing the same spreadsheet 3 times I have given up and now am using the following function to give me the same as EOMONTH

=DATE(YEAR(A1),MONTH(A1)+1,1)

This works and the problem seems to have stopped for now but I am trying to do most of my work in Excel 2003 where there is a guarantee it will open and work.

Some ideas for dealing with this problem.

To find all the cells that have the =#n/a formula use the FIND tool and Search the entire workbook for =#n/a . I have used the Find All feature and then cumbersomely worked through them and corrected all the problems (see our video clip and FIND/ REPLACE to learn how).

Save many versions of your spreadsheet so that if (when) this happens to you, you can go back to a previous version.

Try and avoid the functions that were in the analysis toolpack. Bit difficult because it includes some gems like CUMPRINC, EDATE etc .

With luck a future update will sort this out and return Excel 2007 to the great piece of software that it is.