Working out a tariff from a date and time

Hi All,

I am trying to carry out a count on my data whether it falls within the specific parameters:

Operates on a Monday - Friday,
Within 05:30 - 09:00 or 15:00 - 19:00.

I have tried a combination of SUMPRODUCT and COUNTIFS to no avail, so hoping you could lend a helping hand please?

Data comes in the form of:

Arrival/Departure date time UTC | Arrival/Departure time UTC
01-Jan-11 10:50 | 10:50
01-Jan-11 12:13 | 12:13
02-Jan-11 12:40 | 12:40
02-Jan-11 14:07 | 14:07
02-Jan-11 14:30 | 14:30
...

Thanks in advance,

Charlie.

Thanks for getting back to me 6StringJazzer, and apologies I was not clear.

You are correct about the arrival/departure part, this is not an issue. I need to count whether the occurrence is within the boundaries for the billing scenario.

We currently bill our clients on a peak / off peak:

Peak - Monday to Friday between 05:30 - 09:00 or 15:00 - 19:00.
Off Peak - All other times Monday/Friday - Saturday and Sunday.

is there a way we can combine the WEEKDAY from column A and the times from column B to see what % is peak and off peak?

Many thanks.

I would recommend that you have one column for Departure time and another column for Arrival time. This would make this a lot easier. One problem with your data is that there are an odd number of times. So how do departures correspond to arrivals?

You really need to explain how you want to calculate peak time. For example, if you consider the entire trip "peak" based on departure time alone, then that's different than counting the travel minutes that occur during peak time.

I have attached an example that calculates the portion of each trip that takes place during peak time. For your data, I have assumed that the first time is a departure and the second is arrival, and so on. However, that doesn't seem to make sense for your data, so you'll have to explain it.

I also haven't taken weekends into account, or trips that cross midnight, but first I want to straighten out your definitions.

Hi again 6StringJazzer,

In terms of how does an arrival link to a departure, they dont.

each row refers to an individual movement by an aircraft. i.e arr movement, or dep movement.

I do not need to link each arr movement and dep movement to make a turnaround I simply need to count how many individual times it occurs during a peak period and a non peak period.

ideally I would like to be able to get an output that tells me:

Month / Count of Peak / Count of Off-Peak.
...
Jan / 240 / 350
Feb / 210 / 300 and so on.

please let me know if this is more clear?

Thanks

That is clearer, see attached. Results are on sheet Counts.

Many thanks 6StringJazzer that works great for calculating the peaks and off-peaks, although I have one more thing I hope you can help with.

A flight is only peak if it operates on a Monday to Friday. if it takes place on Saturday it is off peak.

Is there a way we can use WEEKNUM in the countif somehow to include this?

Thanks for all your help so far!

Hi Daddylonglegs,

Firstly this is great, many thanks!

I was wondering if you could assist if I was to add a few more variables in there?

Please see the attached.

I have added airlines and also tariff B/tariff C - both of these are included on each row.

If you review the 'Counts' sheet you will see there are some cells highlighted red which I am struggling to calculate.

Many thanks in advance.