Working out a tariff from a date and time

**excel-failure** · 11-03-2011, 02:01 PM

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.

**6StringJazzer** · 11-03-2011, 04:30 PM

Welcome to the Forum!

I'm not clear on what you're trying to do when you say "whether it falls within." You have arrival and departure in the same column. So do you want to know the count of all times that fall within one of those two ranges? Without regard to what's departure and what's arrival?

Here is an Excel 2007 formula that will do this:

=COUNTIFS(B:B,">=0.229166666666667",B:B,"<=0.375")+COUNTIFS(B:B,">=0.625",B:B,"<=0.791666666666667")

What it does not do: It does not detect whether a given departure/arrival combination intersects one of those ranges. For example, a trip with a departure of 05:00 and an arrival of 09:30 will not be counted.

**excel-failure** · 11-04-2011, 06:55 AM

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.

**6StringJazzer** · 11-04-2011, 08:00 AM

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.

**excel-failure** · 11-04-2011, 09:15 AM

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

**6StringJazzer** · 11-04-2011, 01:20 PM

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

**excel-failure** · 11-09-2011, 04:23 PM

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!

**daddylonglegs** · 11-09-2011, 04:51 PM

You can't do that with COUNTIFS (not without a helper column at least) - here's a different method using SUMPRODUCT

Put a zero in sheet3 D1 (you can format as ;;; if you want to hide) and then use this formula in Counts sheet B2

=SUMPRODUCT((TEXT(Sheet3!A$2:A$1000,"mmm-yy")=TEXT(A2,"mmm-yy"))*(WEEKDAY(Sheet3!A$2:A$1000,2)<6)*(MOD(MATCH(Sheet3!B$2:B$1000,Sheet3!D$1:D$5),2)=0))

That will work for up to 1000 rows of data, increase if necessary

in D2 copied down use this formula

=COUNTIFS(Sheet3!A$2:A$1000,">="&A2,Sheet3!A$2:A$1000,"<="&EOMONTH(A2,0))-B2

see attached

**excel-failure** · 11-11-2011, 10:21 AM

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.