I have 2 date fields that are imported from another program and are in text format. Column A is a start date and Column B is an end date.
An example of what my spreadsheet looks like:
A B 1 10/08/2012 06:38 PST 10/11/2012 16:25 PST 2 09/04/2012 06:05 PST 10/06/2012 14:22 PST 3 08/12/2012 06:18 PST 10/31/2012 24:00 PST
1. I'm trying to calculate the total duration (in hours) between colummn A and column B that fell between the hours of 0600-2200 not counting Sundays or Holidays.
AND
2. If the start date is before the beginning of the month, only calculate from the beginning of the month (and still only calculating hrs 0600-2200 each day excluding Sundays and Holidays). A2 and A3 are examples of that, the calculation would be from 10/1/2012-end date
I've tried several formulas using nested IF statements, DATEDIF, MID, TEXT, SUMPRODUCT .....I'm to the point of just making a mess.
Any ideas or help would be greatly appreciated!
Bookmarks