Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
i need a formula please? 1
- Thread starter ridge
- Start date
Format cells A1 and B1 for date and military time.
Format the result cell as General.
Go to the VB editor, add a module and insert this function:
In the results cell, insert this formula:
Hope this helps!
Format the result cell as General.
Go to the VB editor, add a module and insert this function:
Code:
Public Function FindDelta(dStart As Date, dEnd As Date) As String
Dim lVal As Long
lVal = DateDiff("n", dStart, dEnd)
FindDelta = Format(lVal / 60, "##.##")
End Function
Code:
=finddelta(A1,B1)- Thread starter
- #3
The Public Function is a customized function that you can use, similar to those included with Excel. I just wrote it myself. If you insert it into the workbook (explained below) it will be available in all of the sheets in that book. One thing to note, you may be prompted to Disable/Enable Macros when you first open the workbook. You will need to Enable the macros for the function to work.
From the worksheet:
1. Tools > Macro > Visual Basic Editor
2. Insert > Module
3. Copy and Paste the code above (Public Function ... End Function)
4. File > Close and Return to Microsoft Excel
5. Insert the function call in the desired cell
About the function:
FindDelta needs to be passed 2 dates and returns the calculated difference. The DateDiff function (VBA) returns the difference between the two values in minutes ("n"
. I just convert that to decimal hours (Format() function) so that it will return a value in hours, to 2 decimal places.
Now, when you call this function from a cell, you just have to specify the cells that contain the start and end dates.
Example:
cell F23 is the start date/time
cell M192 is the end date/time
If you want the difference displayed in cell B8, then insert the following formula into cell B8 (General format):
=finddelta(F23,M192)
Let me know if you have any more problems. I can e-mail you a workbook with this function so you can review it for yourself.
From the worksheet:
1. Tools > Macro > Visual Basic Editor
2. Insert > Module
3. Copy and Paste the code above (Public Function ... End Function)
4. File > Close and Return to Microsoft Excel
5. Insert the function call in the desired cell
About the function:
Code:
Public Function FindDelta(dStart As Date, dEnd As Date) As String
Dim lVal As Long
lVal = DateDiff("n", dStart, dEnd)
FindDelta = Format(lVal / 60, "##.##")
End FunctionNow, when you call this function from a cell, you just have to specify the cells that contain the start and end dates.
Example:
cell F23 is the start date/time
cell M192 is the end date/time
If you want the difference displayed in cell B8, then insert the following formula into cell B8 (General format):
=finddelta(F23,M192)
Let me know if you have any more problems. I can e-mail you a workbook with this function so you can review it for yourself.
-
1
- #5
DSI: You'll have to excuse me if I'm just ignorant, but why are you even using VBA?
The problem, as I see it, is that Row 1 and 2 should be formatted as time, while row 3 should be formatted as follows:
[h]:mm
techsupportgirl@home.com
Brainbench MVP for Microsoft Word
The problem, as I see it, is that Row 1 and 2 should be formatted as time, while row 3 should be formatted as follows:
[h]:mm
techsupportgirl@home.com
Brainbench MVP for Microsoft Word
Test it out, DSI. The problem most people have is either:
a) they don't enter the dates so Excel cannot resolve the 24-hour issue.
b) they format all the cells as regular old time cells and expect it to be able to revolve around the clock.
Look at the begin and end time as being a number that is more like a zip code. It's used for reference but it's not quantitative by itself. Does that make sense? I wanted it to sound technical, but it sounds like BS!!
techsupportgirl@home.com
Brainbench MVP for Microsoft Word
a) they don't enter the dates so Excel cannot resolve the 24-hour issue.
b) they format all the cells as regular old time cells and expect it to be able to revolve around the clock.
Look at the begin and end time as being a number that is more like a zip code. It's used for reference but it's not quantitative by itself. Does that make sense? I wanted it to sound technical, but it sounds like BS!!
techsupportgirl@home.com
Brainbench MVP for Microsoft Word
I was hesitant to drop in on your code, but glad I took the leap!!
techsupportgirl@home.com
Brainbench MVP for Microsoft Word
techsupportgirl@home.com
Brainbench MVP for Microsoft Word
- Thread starter
- #10
Thanks to both of you for your help. However I guess I am the one here who is ignorant. I can't get nothing to work.
To be very honest, the visual editor is wayyyy over my head.
I have never had any formal classes on excel. Everything I know is self taught, and I thought I was doing pretty good untl now.(laff)...I consider myself well to do as far as basic formulas, "IF" formulas, pivot tables, etc.
I'l certainly willing to keep trying any ting either of you can say to help.
Thanks again.
To be very honest, the visual editor is wayyyy over my head.
I have never had any formal classes on excel. Everything I know is self taught, and I thought I was doing pretty good untl now.(laff)...I consider myself well to do as far as basic formulas, "IF" formulas, pivot tables, etc.
I'l certainly willing to keep trying any ting either of you can say to help.
Thanks again.
Send me a file. techsupportgirl@home.com
techsupportgirl@home.com
Brainbench MVP for Microsoft Word
techsupportgirl@home.com
Brainbench MVP for Microsoft Word
- Status
Similar threads
- Locked
- Question