Can any Excel wizards help me with a quick problem

Johnnytoxic · 7 Dec 2013 at 10:09

I need to specify two dates.

Then obtain the hours/minutes between the dates BUT extracting separately the hours between 00:30 - 07:30 , and 00:30 - 07:30.

I would have two values. Basically to identify Economy7 hours used between both dates.

Hitman_Leon · 7 Dec 2013 at 10:14

Oh, total of days between dates *7? because all you need is those hours as a total?

Johnnytoxic · 7 Dec 2013 at 10:18

Hitman_Leon said:
Oh, total of days between dates *7? because all you need is those hours as a total?

Between a date range it will pull value1 the total hours/minutes between 00:30 - 07:30 and value2 the total hours/minutes between 07:30 = 00:30.

Hitman_Leon · 7 Dec 2013 at 10:18

If so just give each date a value of 7 and sum the total of days, quick and dirty as I have not used Excel in years.

Ekim · 7 Dec 2013 at 10:19

I'm not in front of my laptop at the moment, posting from my tablet, but something along the lines of.

=(((DATE2-DATE1)*24)*60) - ((14*(DATE2-DATE1))*60)

The first part is to get how many days are between the dates in minutes. The second part is to remove 14 hours (which I think is right based on the hours for Economy 7) to give you the remaining time.

Johnnytoxic · 7 Dec 2013 at 10:26

Ekim said:
I'm not in front of my laptop at the moment, posting from my tablet, but something along the lines of.

=(((DATE2-DATE1)*24)*60) - ((14*(DATE2-DATE1))*60)

The first part is to get how many days are between the dates in minutes. The second part is to remove 14 hours (which I think is right based on the hours for Economy 7) to give you the remaining time.

Thanks, that would give me a close figure.

Say if I set my start time to 07/12 @22:00 and end time 08/12 @22:00

it would need to pick out the first 2hr 30min for peak rate, then 7hr Economy7, then again 14hr 30min peak.

So between the date specified ( can start at any time ):
17 hours Peak
7 hours E7