Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

vba functions difference

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Vba functions - difference between times in hours

Vba functions - difference between times in hours

ResolvedVersion 2007

Steven has attended:
Access Intermediate course

Vba functions - difference between times in hours

Hi,

I am trying to write a function which gives me the amount of hours an employee is scheduled to work on any given day.

I have a start time formatted as time (like 08:00) and a finish time (like 16:00) in the same format.

Directly in the excel sheet i can apply the formula -
=TEXT(finishtime - starttime,"H") and this works fine and would return 8 for the hours above.

in vba i have written the following funtion -

********************************************

Function shiftTime(FT As Date, ST As Date) As Integer

shiftTime = (FT - ST)

End Function
********************************************

This is returning 0, i think because of formatting?

How could i write the function similar to the excel formula example i have given? I have tried inserting the word TEXT and the "H" but does not seem to work in any combination i have tried.

Regards
Steve

RE: vba functions - difference between times in hours

Hi Steve. You're almost there:

-----------------
Function shiftTime(FT As Date, ST As Date) As Integer

shiftTime = Application.WorksheetFunction.Text((FT - ST), "H")

End Function
-----------------

Hope this helps,

Anthony

RE: vba functions - difference between times in hours

Thanks Anthony,

Worked perfectly!

Regards
Steve

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Creating charts in Excel 2010

Here's how to present your data in a chart format:

Highlight the data you wish to use in a chart

Click the Insert Ribbon

In the Charts Group, select Column Chart

Your chart will then appear on your work sheet.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.24 secs.