vba functions difference

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

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

Vba functions - difference between times in hours | Excel forum

resolvedResolved · High Priority · Version 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:

Moving between Worksheets without using the mouse

Use the 'Ctrl+PgDn' and 'Ctrl+PgUp' keys.

'Ctrl+PgDn' will move to the right and 'Ctrl+PgUp' will move to the left one worksheet at a time.

View all Excel hints and tips


Server loaded in 0.07 secs.