Wrox Programmer Forums Calculating workhours with 2 overtime intervalls. VBA (Excel)
 |
 Excel VBA Discuss using VBA for Excel programming.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

January 30th, 2011, 07:04 AM
 JanBanan Registered User Join Date: Jan 2011 Posts: 1 Thanks: 0 Thanked 0 Times in 0 Posts
Calculating workhours with 2 overtime intervalls. VBA (Excel)

Hello.

I have a Form where the user enters Start-time and Stop-time.
The form has four fields, T1 for start hour (0-24) and M1 for minutes,
T2 and M2 for end of workday. Ex: 09 45 and 18 00.

I have to calculate two different overtime values.
First is overtime between 15:00 to 21:00
Second is overtime from 21:00 to 07:00 (next morning)

Normal worktime, whitout overtime is 07:00 to 15:00

I want the resulting overtime to be in the format h,m (3,30 for 3 hours and 30 minutes)

Can anyone here show me a nice solution for this?

1.
User enter 14 00 as start of work and 22 45 as end.
This would give an overtime calculation of 6 hours for time between 15:00 and 21:00 (21-15) and 1 hour 45 minutes for overtime between 21:00 and 07:00 (22,45 - 21)

2.
User enter 05 50 as start of work and 16 00 as end.
This would give an overtime calculation of 1 hour for overtime between 15:00 and 21:00 (16-15) and 1 hour and 10 minutes for time between 21:00 and 07:00
(7-5,50)

3.
User enter 23 30 as start of work and 04 00 as end.
This would give an overtime calculation of 4 hours and 30 minutes
(24-23,30+4) for overtime between 21:00 and 07:00

Hope this makes things clearer.

 Similar Threads Thread Thread Starter Forum Replies Last Post #NAME? error running Excel 2003 VBA in Excel 2007 steveburn Excel VBA 0 October 24th, 2009 08:47 AM Help! Conversion Excel 2003 VBA codes to Excel 2007 sunny76 BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 0 August 13th, 2009 05:38 AM Code works in Excel VBA but not Access VBA fossx Access VBA 2 May 21st, 2007 08:00 AM Calculating numbers of days similar to Excel klott16 Access 7 January 3rd, 2006 02:43 PM Excel VBA to SQL & back to VBA edesousa Excel VBA 1 June 1st, 2004 02:39 AM