Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)

share|improve this question
up vote 6 down vote accepted

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").

share|improve this answer
    
This is called a UDF, user-defined function. – Lance Roberts Sep 22 '09 at 15:12
    
Sounds perfect, trying it now. – C. Ross Sep 22 '09 at 15:32

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .