Formulas with times past midnight big help required

[Thoron6]

OK, I've struggled with this for most of today so any help would be gratly appreciated....

I have data for each HH of the week arranged as follows:

Sun 00:30 Sun 01:00 sun 01:30 etc to sat



Below this I have Seperate day opening / closing hours:

Monday 11am - 11pm
Tuesday 11am - 2am
etc

I amtrying to calculate on another row (Calculations work fine) but am having a massive problem when a site closes past midnight. So the formula I have looks up the start time and end time and calculates based on outside of these hours. Where a property shuts after midnight it is redirecting the formula (If time of day is greater than opening time and less than closing time) to the morning of that same day....

If anyone can assist that would be great.

Thank you!

 

[Jacko058]

Do you have an example file?

 

[Dave Patton]

Your question is not clear. Please provide several examples and expected results.

Excel 2010
	A	B	C	D	E
2	Monday	11:00 AM	11:00 PM	12:00	12.0
3	Tuesday	11:00 AM	2:00 AM	15:00	15.0
4
4d

Cell Formulas
Range	Formula
D2	=C2-B2+(B2>C2)
D3	=C3-B3+(B3>C3)
E2	=(C2-B2+(B2>C2))*24
E3	=(C3-B3+(B3>C3))*24

 

[Thoron6]

Sorry, New to this and not got the spread sheet in front of me.

So I've drawn the below:

Mon

This date changes based on a lookup from another sheet

Sun

Sun

Sun<strike></strike>

Sun​<strike></strike>

Sun​

Sun​

Sun​

Sun

Sun<strike></strike>

Sun

Sun<strike></strike>

Sun

Sun<strike></strike>

Sun

Sun<strike></strike>

Sun

Sun<strike></strike>

Sun

Sun<strike></strike>

Sun

Sun<strike></strike>

Sun

Sun<strike></strike>

Sun

Sun<strike></strike>

Sun

Sun<strike></strike>

Sun

Sun<strike></strike>

<strike></strike>00:30

01:00<strike></strike>

<strike></strike>01:30

02:00

02:30

03:00

03:30

04:00

04:30

05:00

05:30

06:00

06:30

07:00

07:30

08:00

08:30

09:00

09:30

10:00

2

3

4

5

6

5

4

7

8

9

5

4

1

2

3

5

4

7

9

5

Mon

11:00

23:00

Tue

10:00

00:00

Wed

06:00

00:00

Thu

10:00

02:00

Fri

10:00

23:00

Sat

11:00

00:00

Sun

10:00

03:00

The below is a unique reference to

Sun00:30

sun01:00

Sun01:30

Sun02:00

Sun02:30<strike></strike>

<strike></strike>Sun03:00

xx

xx

xx

xx

xx

xx

<tbody>
</tbody>

So the day at the top changes based on another sheet, this will change the unique cells to match. I then need the "xx" figures based on only the closed period of the property (Based on the opening and closed times above)

I hope this helps and is more clear?

Thanks

 

[Thoron6]

So in the example above, for Sunday I would need blanks up to 10am then the "5" and any other data up to Monday 3am.