Calculating number of days when you don't have an end date

[ExcelGirl_215]

I need to determine how many days during a month a person was in our program. For instance, if someone is in the program from 1/1/2013 to 4/5/2013, then he/she was here 31 days in January, 28 in February, 30 in March, and 5 in April.

The issue I’m running into is that some people do not have an end date because they are currently in the program. So if someone began the program on 1/1/2013 and is still in the program (i.e., has no end date), they would have 31 days in Jan, 28 in Feb, 30 in March, and 30 in April (and so on). However, I’m not sure how to create a formula that will account for the lack of an end date for most of the people in the spreadsheet.

Any ideas??

 

[Scott Huish]

PUT =TODAY() in a cell, say A1

Then whereever in your formula you refer to the end date, I am going to use E1 in this example, instead of E1, use MAX(E1,A1)

 

[Jonmo1]

Can you post a formula you already have that gives the desired result when there IS an end date?

Should be easy enough to do something like

IF(B1="",TODAY(),B1)

If B1 is supposed to be the end date, then it substitutes B1 with today's date when B1 is empty.

 

[circledchicken]

Hi,

Assuming all available end dates are before 'today', maybe:

Excel 2013

	A	B	C	D	E
Start date	End date	Days	Today

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01/01/2013[/TD]
[TD="align: right"]04/05/2013[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"][/TD]
[TD="align: right"]09/05/2013[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01/01/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]247[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=TODAY()[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=MIN(B2, E$2) - A2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ExcelGirl_215]

Thanks everyone for your speedy help!

This is the formula I'm using right now (with the date changed for each month)

=MAX(MIN(D9+1, "7/31/2013"+1),C9) - MIN(MAX(C9,"7/1/2013"),D9+1)

Where D9 is the end date in the program and C9 is the start date. This yields the correct number of days someone was in the program when they have an end date.

 

[Gra06021]

F16= Start date
G16= End Date

=NETWORKDAYS(F16,IF(ISBLANK(G16),TODAY(),G16))

If you end date is blank (they are still working for you) then it will give the number of days that they have worked between their start day and the current date.

 

[Jonmo1]

Then you can apply the logic as such

=MAX(MIN(IF(D9="",TODAY(),D9)+1, "7/31/2013"+1),C9) - MIN(MAX(C9,"7/1/2013"),IF(D9="",TODAY(),D9)+1)

or using min as others suggested

=MAX(MIN(MIN(TODAY(),D9)+1, "7/31/2013"+1),C9) - MIN(MAX(C9,"7/1/2013"),MIN(TODAY(),D9)+1)

 

[ExcelGirl_215]

Jonmo1 thank you! That's exactly what I needed.

 

[Jonmo1]

Glad to help, thanks for the feedback...


Actually, I think that should have been MAX, not MIN...
=MAX(MIN(MAX(TODAY(),D9)+1, "7/31/2013"+1),C9) - MIN(MAX(C9,"7/1/2013"),MAX(TODAY(),D9)+1)

but anyway, glad you got it working.

 

[ExcelGirl_215]

The first formula worked, but this one just says everyone has 31 days in July...so I'm going to stick with the first one

Thanks again!