MS Excel: DATEDIF function

The DATEDIF function can be used to calculate the number of days/moths/years between two dates in versions of Excel from 97 to 2007.

=DATEDIF(B9,C9,"m")/12

=DATEDIF(B9,TODAY(),"d")

=DATEDIF(B9,C9,"m")

It is interesting to note that DATEDIF is not listed with other functions under the formula tab in Excel 2007.

Folder and File Option : Missing Security Tab: Windows XP

Problem: Right clicking on folder or file not showing security tab:


Go to Explorer -> tools -> folder options -> View->

navigate to the last check box in the list “USE SIMPLE FILE SHARING” .

Uncheck this checkbox and click OK.

Now you will be able to see the security tab in file properties.

Allow only numeric value in a textbox on a form

Remove //** From the html codes:

This is the function in the header tag

function AllowNumeric(e) {
var iKeyCode = 0;
if (window.event)
iKeyCode = window.event.keyCode
else if (e)
iKeyCode = e.which;
if (iKeyCode > 47 && iKeyCode < 58)
return true
else
return false;

Here is the HTML for the textbox

//////(<)//**
INPUT onkeypress="javascript: return AllowNumeric(event);" id="txtAcctNo" style="WIDTH: 120px; HEIGHT: 20px"
type="text" maxLength="8" runat="server" size="14"
//**>///////

The VLOOKUP Function:Very useful and very simple

The most useful and least used in common practice due to lack of knowledge is the VLOOKUP function of MS Excel.
Here I am presenting with the help of one example the common use of VLOOKUP function.
Let us first see the function:
With the VLOOKUP function, ypu can look for specified data in the first column of a table. The result is returned from the column number specified, if found.The formula for VLOOKUP is:

=vlookup(lookup_value,table_array,col_index_num,range_lookup)



Let us look at the example, I want to see if value in column A3 to A6 of table A exist in Table B.If yes, I want formula to return me column E's value.
The formula is:

=+VLOOKUP(A3,$E$3:$F$6,1,FALSE)

Now, here I am looking for A3(lookup_value) of table A in E3 to F6(table_array), remember: it will look into left most column,so in E3 to E6,and return me value from column 1(col_index_num) which is E3 to E6.Then comes the range_lookup which is False.The Reason for it being False is,the use of False as the optional range_lookup Argument. This directs to the VLOOKUP to find an exact match and is most often needed when looking for a text match. If this is omitted, or True, you will often get unwanted results when searching for text that is in an unsorted column of data. When True is used, or the range_lookup Argument is ignored, the data should be sorted (by the first column) in ascending order.

See the results in column A8 to A11.

Now, if I want to return the value from column F of Table B, all I have to do is to change column index to 2.

=+VLOOKUP(A3,$E$3:$F$6,2,FALSE)

See the results in column D8 to D11.

Whenever, match is not found, the formula will retorn an error i.e. #N/A.To avoid this, i write another formula:

=IF(ISERROR(+VLOOKUP(A3,$E$3:$F$6,1,FALSE))=TRUE,0,(+VLOOKUP(A3,$E$3:$F$6,1,FALSE)))

Meaning that if vlookup returns error i.e. #N/A, then return 0 else return vlookup value.
That is all to it.Hope you find it useful.

Export Custom Outlook Form Data to MS Access

Dim appAccess
Dim nms
Dim strFolder
Dim fld
Dim strAccessPath
Dim rst
Dim dbe
Dim wks
Dim dbs
Dim itms
Dim itm

Sub CommandButton1_Click()

Set nms = Application.GetNamespace("MAPI")
strFolder = "fbtest folder"
Set fld = nms.Folders("Personal Folders").Folders(strFolder)

'Pick up path to Access database directory from Access SysCmd function
Set appAccess = CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)

'Get DAO version from DBEngine
strDBEngine = appAccess.Application.DBEngine.Version

'MsgBox "DBEngine version: " & strDBEngine

appAccess.Quit

If strDBEngine = "3.51" Then
'Office 97 DAO version
Set dbe = CreateObject("DAO.DBEngine.35")
strDBName = strAccessPath & "fbtestdb.mdb"
ElseIf strDBEngine = "3.6" Then
'Office 2000 DAO version
Set dbe = CreateObject("DAO.DBEngine.36")
strDBName = strAccessPath & "fbtestdb.mdb"

Else

MsgBox "Unknown Office version; canceling"

Exit Sub

End If
'MsgBox "DBName: " & strDBName
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase("S:\sameer\fbtestdb.mdb")

'Open Access table containing contact data
Set rst = dbs.OpenRecordset("fbtesttable")
'Set up reference to Outlook folder of items to export

Set itms = fld.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No FB requests to export"

Exit Sub
Else

MsgBox ItemCount & " FB requests to export"

End If
'Set up reference to Outlook folder of items to export
Set itms = fld.Items
ItemCount = itms.Count

If ItemCount = 0 Then

MsgBox "No Fund Builder requests to export"

Exit Sub
Else

MsgBox ItemCount & " FB requests to export"

End If
For Each itm In itms

rst.AddNew

'Custom Outlook properties
rst.Request=itm.userproperties("001 Request")
rst.AccountNumber=itm.userproperties("002 Account Number") rst.ClientName=itm.userproperties("002 Client Name")

rst.Update

Next

rst.Close

MsgBox "All FB requests exported!"

End Sub