So I've got a spreadsheet which has a "Start Time/Date" in one cell (A1) and an "End Time/Date" in the next (B1). I have it figured out how to calculate the duration in minutes, (B1-A1)*1440. Easy enough.
Here's my problem. I need to figure out how to keep the "Start Time/Date" in one cell and "End Date/Time" in the other cell while being able to count "Peak" and "Non-Peak" minutes.
For example, say I have an incident that starts 06/30/2008 4:30 PM and ends 06/30/2008 7:00 PM. Peak time is from 8:00 AM - 6:00 PM on weekdays. I want to be able to show that 90 minutes should be distributed to a "Peak Minutes" cell and the final 60 minutes should be distributed to a "Non-Peak Minutes" cell.
To further complicate things, I need to show "Peak" and "Non-Peak" minutes for incidents that span multiple days, where Saturday and Sunday are considered "Non-Peak" along with 12:00 AM - 7:59 AM and 6:01 PM - 11:59 PM on weekdays.
Is there any hope?
Here's my problem. I need to figure out how to keep the "Start Time/Date" in one cell and "End Date/Time" in the other cell while being able to count "Peak" and "Non-Peak" minutes.
For example, say I have an incident that starts 06/30/2008 4:30 PM and ends 06/30/2008 7:00 PM. Peak time is from 8:00 AM - 6:00 PM on weekdays. I want to be able to show that 90 minutes should be distributed to a "Peak Minutes" cell and the final 60 minutes should be distributed to a "Non-Peak Minutes" cell.
To further complicate things, I need to show "Peak" and "Non-Peak" minutes for incidents that span multiple days, where Saturday and Sunday are considered "Non-Peak" along with 12:00 AM - 7:59 AM and 6:01 PM - 11:59 PM on weekdays.
Is there any hope?
