Calculating number of days between multiple dates with blank cells

[gelliebeanz]

Hi All,

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:

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Week[/TD]
[TD]Room Number[/TD]
[TD]Name[/TD]
[TD]Check In[/TD]
[TD]Check Out[/TD]
[TD]R&R In[/TD]
[TD]R&R Out[/TD]
[TD]On Site Days[/TD]
[TD]Off Site Days[/TD]
[TD]Unoccupied Days[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]A1[/TD]
[TD]John Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]01/04/13[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]A1[/TD]
[TD]John Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]09/04/13[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]A2[/TD]
[TD]Mary Smith[/TD]
[TD]02/04/13[/TD]
[TD][/TD]
[TD][/TD]
[TD]07/04/13[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]A2[/TD]
[TD]Mary Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]09/04/13[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]A2[/TD]
[TD]Mary Smith[/TD]
[TD][/TD]
[TD]13/04/13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[par60056]

I looked at what you asked for and the results that you provided. (Thank you that is a very helpful way to get answers that help)

I am assuming that Sheet 1 is actually a table with the name in A and the value in B. Not sure why you need to define these unless a week could be other than 1st-7th, 8th-14th, 15th-21st, 22nd-28th, 29th-end The dates can all be calculated from the week number and the month number.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]startmonth[/TD]
[TD]1/4/2013[/TD]
[/TR]
[TR]
[TD]endmonth[/TD]
[TD]30/4/2013
[/TD]
[/TR]
[TR]
[TD]week1start[/TD]
[TD]1/4/2013[/TD]
[/TR]
[TR]
[TD]week1end[/TD]
[TD]7/4/2013[/TD]
[/TR]
[TR]
[TD]week2start[/TD]
[TD]8/4/2013[/TD]
[/TR]
[TR]
[TD]week2end[/TD]
[TD]14/4/2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


1 thing you don't say is what it means to have D blank. I think it means that it is the start of the week.

I have some complex formulas for you to try but when I started them I assumed the H & I only had values if D had a value. It is more complex if D does not have a value.

Would it be possible to put a formula into D to make it default? Then override the formula by putting a value in D4? I understand if this is not possible because you want it to be more obvious when it isn't the start of the week.

The formula for D would be something like:
=VLOOKUP("week" & A2 & "start",Sheet1!$A$1:$B$10,2,0)

IF D always has a value then the formulas are :
H2= =IF(E2<>"", E2-D2,IF(F2<>"","Error",IF(G2<>"",G2-D2,VLOOKUP("week" & A2 & "end",Sheet1!$A$1:$B$8,2,0)-D2)))
I2= =IF(E2<>"","",IF(F2<>"","Error",IF(G2<>"",VLOOKUP("week" & A2 & "end",Sheet1!$A$1:$B$8,2,0)-G2+1,"")))

If you need to keep D blank, try this in H:
H2= =IF(D2="",IF(E2<>"", E2-VLOOKUP("week" & A2 & "start",Sheet1!$A$1:$B$8,2,0),IF(F2<>"","Error",IF(G2<>"",G2-VLOOKUP("week" & A2 & "start",Sheet1!$A$1:$B$8,2,0),VLOOKUP("week" & A2 & "end",Sheet1!$A$1:$B$8,2,0)-VLOOKUP("week" & A2 & "start",Sheet1!$A$1:$B$8,2,0)))),IF(E2<>"", E2-D2,IF(F2<>"","Error",IF(G2<>"",G2-D2,VLOOKUP("week" & A2 & "end",Sheet1!$A$1:$B$8,2,0)-D2))))

I think there is still an issue. The initial post did not say what happens if D is blank and F is not blank. So this formula could get more complex with another big IF


Hope this helps some. It was fun to figure out.

 

[gelliebeanz]

Hi Par60056,

Thank you for your fast response!

To clarify and answer your queries:

The reason why I wanted to name the cells was because this spreadsheet was going to be used by a couple of other people and I wanted to make it as user friendly as possible, ie label the cells they were allowed to enter data into and then have the number of days automatically calculated. I thought by naming the cells, it would remove the need for the VLOOKUP but I don't know if understand the naming of cells feature properly (eg instead of "VLOOKUP("week" & A2 & "start",Sheet1!$A$1:$B$8,2,0)" we could just used "Week1Start" ?)

I should have been more clear from the beginning. This is sort of a rolling spreadsheet, but each month will be saved as a new file. Therefore, sometimes D2 will be blank, if for example a customer checked in on 28/03/13 but remained on site and didn't go on R&R until 01/04/13.

In this instance, if D was blank and F had a date, then there's 2 possibilites for On Site Days:
If G was blank, then return Week1End - R&R in + 1
If G was not blank, then return R&R Out - R&R In

And then in Off Site Days:
If G was blank, then return Week1Start - R&R In
If G was not blank, then return R&R Out - R&R In - 1

I really appreciated your help.

Thank you.

 

[par60056]

Hi Par60056,

Thank you for your fast response!

To clarify and answer your queries:

The reason why I wanted to name the cells was because this spreadsheet was going to be used by a couple of other people and I wanted to make it as user friendly as possible, ie label the cells they were allowed to enter data into and then have the number of days automatically calculated. I thought by naming the cells, it would remove the need for the VLOOKUP but I don't know if understand the naming of cells feature properly (eg instead of "VLOOKUP("week" & A2 & "start",Sheet1!$A$1:$B$8,2,0)" we could just used "Week1Start" ?)

I should have been more clear from the beginning. This is sort of a rolling spreadsheet, but each month will be saved as a new file. Therefore, sometimes D2 will be blank, if for example a customer checked in on 28/03/13 but remained on site and didn't go on R&R until 01/04/13.

In this instance, if D was blank and F had a date, then there's 2 possibilites for On Site Days:
If G was blank, then return Week1End - R&R in + 1
If G was not blank, then return R&R Out - R&R In

And then in Off Site Days:
If G was blank, then return Week1Start - R&R In
If G was not blank, then return R&R Out - R&R In - 1

I really appreciated your help.

Thank you.

That was sort of what I thought it meant when D was blank.

I don't used named ranges much. so you may be correct in it would be easier to reference them by name than using the vlookup. But you would still need to build the name for the right week. So instead of "VLOOKUP("week" & A2 & "start",Sheet1!$A$1:$B$8,2,0)" you would reference "Range("Sheet1!week" & A2 & "start")" (I think)

The rest of the formula is essentially the same. I think I found a little problem in the On Site Days calculation. I have updated it.

H2= =IF(D2="",IF(F2="",IF(E2<>"", E2-VLOOKUP("week" & A2 & "start",Sheet1!$A$1:$B$8,2,0),IF(G2<>"",G2-VLOOKUP("week" & A2 & "start",Sheet1!$A$1:$B$8,2,0),VLOOKUP("week" & A2 & "end",Sheet1!$A$1:$B$8,2,0)-VLOOKUP("week" & A2 & "start",Sheet1!$A$1:$B$8,2,0))),IF(E2<>"", "Error",IF(G2<>"",G2-F2,VLOOKUP("week" & A2 & "end",Sheet1!$A$1:$B$8,2,0)-F2))),IF(E2<>"", E2-D2,IF(F2<>"","Error",IF(G2<>"",G2-D2,VLOOKUP("week" & A2 & "end",Sheet1!$A$1:$B$8,2,0)-D2))))

I am still a little unclear on the calculation for off site days. This gets a little move complex if somebody took off only 2 days in the middle of a week.

Would it be possible to have a row like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A1[/TD]
[TD]John Smith[/TD]
[TD][/TD]
[TD]6/4/13[/TD]
[TD]5/4/13[/TD]
[TD]2/4/13[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I think to properly take all possibilities into account and be able to understand this you might want to think about a user defined function in VBA. There are just too many permutations of dates to be able to write it in a clean formula and have much hope of maintaining it.