How can I create a reusable function in Excel 2007?

Question

How can I create a resuable function/formula I can use in formulas in a spreadsheet? In particular I need a very simple DateDiff formula and would like a way to encapsulate this function:

=(((HOUR(B3) * 60) * 60) + (MINUTE(B3) * 60) + SECOND(B3)) - (((HOUR(A3) * 60) * 60) + (MINUTE(A3) * 60) + SECOND(A3))

(Thanks to Phil Winstanley)

Answer 1

This link completely describes how to do it... instructions have been enumerated below:

1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Enter the Excel User Defined Function (UDF) in VBA
5. Get out of VBA (Press Alt+Q)
6. Use the functions (in the worksheet) - They will appear in the Paste Function dialog box, Shift+F3, under the "User Defined" category)

It's for Excel-2003, but the process is the same

You create a VBA module with a function, say NEWfunction,
then reference it in the cell, =NEWfunction("A1").

Answer 2

While a VBA UDF is probably the best way to go. The formula you have could be simplified to

=MOD((B3-A3)*86400,86400)

because of the way Excel stores Date/Times as doubles