C
Craig
I am trying to write a macro that will automatically calculate and display,
in seperate columns, the number of hours worked in both Peak (09:00 to
19:00 - Mon - Fri) and off peak (19:01 to 08:59 Mon - Fri and all day
Saturday and Sunday), as well as to split those between Invoiced and No
Charge hours. These hours are then to be subtotalled by Suite.
The data I am working from looks as follows:
WorkSheetNo Date Suite RateCardItem Product Job Name Status Start End
W124980 20/07/2004 Room 1 Video1 Product1 Job1 No Charge 11:00 02:00
W124981 21/07/2004 Room 1 Video2 Product2 Job2 Invoiced 03:00 02:00
W124982 22/07/2004 Room 2 Video1 Product3 Job3 No Charge 17:00 18:00
W124983 23/07/2004 Room 2 Video2 Product4 Job4 Invoiced 17:00 04:00
W124984 24/07/2004 Room 3 Video1 Product5 Job6 No Charge 07:00 23:00
W124985 25/07/2004 Room 3 Still1 Product6 Job5 Invoiced 07:00 06:00
The output should look something like this, though the formatting is not
important:
Date Suite RateCardItem Status Start End Actual Hrs Peak Hrs Off Peak
Hrs No Charge Peak Hrs No Charge Off Peak Hrs
22/07/2004 Suite1 Product1 Invoiced 10:00 11:00 1.0 1.0
21/07/2004 Suite1 Product1 Invoiced 18:30 19:30 1.0 0.5 0.5
20/07/2004 Suite1 Product2 No Charge 17:00 18:00 1.0 1.0 1.0
02/07/2004 Suite1 Product2 Invoiced 18:30 19:15 0.8 0.5 0.3
02/07/2004 Suite1 Product2 Invoiced 14:00 18:00 4.0 4.0
01/07/2004 Suite1 Product2 Invoiced 09:00 11:00 2.0 2.0
Suite1 Total 9.8 9.0 0.8 1.0 -
30/07/2004 Suite2 Product1 Invoiced 10:00 10:15 0.3 0.3
29/07/2004 Suite2 Product1 Invoiced 17:00 19:30 2.5 2.0 0.5
13/07/2004 Suite2 Product2 No Charge 17:00 21:00 4.0 2.0 2.0
09/07/2004 Suite2 Product2 Invoiced 12:00 19:00 7.0 7.0
09/07/2004 Suite2 Product2 Invoiced 08:00 11:00 3.0 2.0 1.0
08/07/2004 Suite2 Product2 Invoiced 09:00 13:30 4.5 4.5
08/07/2004 Suite2 Product2 Invoiced 16:30 20:30 4.0 2.5 1.5
Suite2 Total 25.3 18.3 3.0 2.0 2.0
Any help would be greatly appreciated.
Thanks in advance.
Craig
in seperate columns, the number of hours worked in both Peak (09:00 to
19:00 - Mon - Fri) and off peak (19:01 to 08:59 Mon - Fri and all day
Saturday and Sunday), as well as to split those between Invoiced and No
Charge hours. These hours are then to be subtotalled by Suite.
The data I am working from looks as follows:
WorkSheetNo Date Suite RateCardItem Product Job Name Status Start End
W124980 20/07/2004 Room 1 Video1 Product1 Job1 No Charge 11:00 02:00
W124981 21/07/2004 Room 1 Video2 Product2 Job2 Invoiced 03:00 02:00
W124982 22/07/2004 Room 2 Video1 Product3 Job3 No Charge 17:00 18:00
W124983 23/07/2004 Room 2 Video2 Product4 Job4 Invoiced 17:00 04:00
W124984 24/07/2004 Room 3 Video1 Product5 Job6 No Charge 07:00 23:00
W124985 25/07/2004 Room 3 Still1 Product6 Job5 Invoiced 07:00 06:00
The output should look something like this, though the formatting is not
important:
Date Suite RateCardItem Status Start End Actual Hrs Peak Hrs Off Peak
Hrs No Charge Peak Hrs No Charge Off Peak Hrs
22/07/2004 Suite1 Product1 Invoiced 10:00 11:00 1.0 1.0
21/07/2004 Suite1 Product1 Invoiced 18:30 19:30 1.0 0.5 0.5
20/07/2004 Suite1 Product2 No Charge 17:00 18:00 1.0 1.0 1.0
02/07/2004 Suite1 Product2 Invoiced 18:30 19:15 0.8 0.5 0.3
02/07/2004 Suite1 Product2 Invoiced 14:00 18:00 4.0 4.0
01/07/2004 Suite1 Product2 Invoiced 09:00 11:00 2.0 2.0
Suite1 Total 9.8 9.0 0.8 1.0 -
30/07/2004 Suite2 Product1 Invoiced 10:00 10:15 0.3 0.3
29/07/2004 Suite2 Product1 Invoiced 17:00 19:30 2.5 2.0 0.5
13/07/2004 Suite2 Product2 No Charge 17:00 21:00 4.0 2.0 2.0
09/07/2004 Suite2 Product2 Invoiced 12:00 19:00 7.0 7.0
09/07/2004 Suite2 Product2 Invoiced 08:00 11:00 3.0 2.0 1.0
08/07/2004 Suite2 Product2 Invoiced 09:00 13:30 4.5 4.5
08/07/2004 Suite2 Product2 Invoiced 16:30 20:30 4.0 2.5 1.5
Suite2 Total 25.3 18.3 3.0 2.0 2.0
Any help would be greatly appreciated.
Thanks in advance.
Craig