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

Sorry, formatting disappeared. Will try again under separate post.

Craig


"Craig" wrote in message
...
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

...an anwer Craig? I'm trying to do something similar and wouldn't min
any ideas

--
nikolas
-----------------------------------------------------------------------
nikolasp's Profile: http://www.hightechtalks.com/m40
View this thread: http://www.hightechtalks.com/t133777