#### gelliebeanz

##### New Member

- Joined
- Apr 15, 2013

- Messages
- 2

I don't even know if this is even possible but here goes... This is based on an accommodation type of business for a mine site.

On Sheet1 I have named a few cells being:

StartMonth = 01/04/13

EndMonth = 30/04/13

Week1Start = 01/04/13

Week1End = 07/04/13

Week2Start = 08/04/13

Week2End = 14/04/13

...etc for the whole month of April 2013

On Sheet2 I have a schedule for the rooms similar to this:

A | B | C | D | E | F | G | H | I | J | |

1 | Week | Room Number | Name | Check In | Check Out | R&R In | R&R Out | On Site Days | Off Site Days | Unoccupied Days |

2 | 1 | A1 | John Smith | 01/04/13 | 7 | |||||

3 | 2 | A1 | John Smith | 09/04/13 | 1 | 6 | ||||

4 | 1 | A2 | Mary Smith | 02/04/13 | 07/04/13 | 5 | 1 | 1 | ||

5 | 2 | A2 | Mary Smith | 09/04/13 | 4 | 1 | ||||

6 | 2 | A2 | Mary Smith | 13/04/13 | 2 |

<tbody>

</tbody>

Column A shows which week of the month it is.

What I need is number of days to be returned for On Site Days, Off Site Days and Unoccupied, per the red cells.

I'm the furtherest thing from a programmer you can find, and my Excel skills are intermediate at best, so please bear with me while I try and explain as best I can.

Basically this is what I want is:

**In Column H (On Site Days):**

If there's a value in Check In, then check for value is Check Out.

If there is a value in Check Out, then return Check Out - Check In.

If Check Out is blank, then check for value in R&R In.

If there is a value in R&R In the return "Error".

If R&R In is blank, then check for value in R&R Out.

If there is a value in R&R Out, then return R&R Out - Check In.

If R&R Out is blank, then return Week1End - Check In.

**In Column I (Off Site Days):**

If there is a value in Check In, Then check for value in Check Out.

If there is a value in Check out, then return nothing.

If Check Out is blank, then check for value in R&R In.

If there is a value in R&R In, then return "Error".

If R&R In is blank, then check for value in R&R Out.

If there is a value in R&R Out, then return Week1End - R&R Out.

If R&R Out is blank, then return nothing.

**In Column J (Unoccupied Days):**

Just be a "simple" formula similar to "=7-G2-H2".... so the sum of those 3 cells for that week should equal 7.

I don't know if I have covered off all bases in the above (or even if it's correct!), but I hope it's enough to get the feel what I am looking for.

I have probably already spent too much time researching and attempting this, so any help would be greatly appreciated.

Many thanks in advance.