A
Altec101
Hello Folks,
I need to create a macro that will be able to determine the total
amount of time from 8:30am till 4:30pm between two columns (column A
holds the start time and column B holds the close time) and have it
insert into column C the total amount of time calculated between two
dates and times between the hours of 8:30am and 4:30pm Monday through
Friday, not including holidays.
The start date could be something like 16/05/2006 1:30:02 PM and the
end date could be 18/05/2006 3:30:02 PM, 2 days from the start time. I
need the total amount of time there was from 1:30pm till 4:30pm on
16/05/2006 the total amount of time for the next day between 8:30 and
4:30 on 17/05/2006 and the total amount of time on the closing date
between 8:30 and 3:30.(3:30 being the time the report was closed).
I only need the times between business hours for each day (which are
8:30am to 4:30pm) from Monday to Friday and have it exclude holidays
too.
Example #2: If a report was created after 4:30pm(start time) and not
closed until 1:30pm(closed time) the next day, the only time counted
would be from 8:30 until 1:30 the following day, because the only time
that falls between 8:30 to 4:30 time is on the following day.
Example#2: If a report was created Monday at 11:30am (start time) and
not closed until 3:30pm (closed time) Wednesday, then the time counted
would be from 11:30am until 4:30pm same day the report was created,
Tuesday from 8:30am to 4:30pm and Wednesday from 8:30am until 3:30pm
because the report was open from Monday to Wednesday and only the time
between 8:30 and 4:30 was counted for them days.
I need to create a macro that will be able to determine the total
amount of time from 8:30am till 4:30pm between two columns (column A
holds the start time and column B holds the close time) and have it
insert into column C the total amount of time calculated between two
dates and times between the hours of 8:30am and 4:30pm Monday through
Friday, not including holidays.
The start date could be something like 16/05/2006 1:30:02 PM and the
end date could be 18/05/2006 3:30:02 PM, 2 days from the start time. I
need the total amount of time there was from 1:30pm till 4:30pm on
16/05/2006 the total amount of time for the next day between 8:30 and
4:30 on 17/05/2006 and the total amount of time on the closing date
between 8:30 and 3:30.(3:30 being the time the report was closed).
I only need the times between business hours for each day (which are
8:30am to 4:30pm) from Monday to Friday and have it exclude holidays
too.
Example #2: If a report was created after 4:30pm(start time) and not
closed until 1:30pm(closed time) the next day, the only time counted
would be from 8:30 until 1:30 the following day, because the only time
that falls between 8:30 to 4:30 time is on the following day.
Example#2: If a report was created Monday at 11:30am (start time) and
not closed until 3:30pm (closed time) Wednesday, then the time counted
would be from 11:30am until 4:30pm same day the report was created,
Tuesday from 8:30am to 4:30pm and Wednesday from 8:30am until 3:30pm
because the report was open from Monday to Wednesday and only the time
between 8:30 and 4:30 was counted for them days.