Calculating time elapsed between two dates (not times) and related date format problem

[dohraemon]

Hey, I'm pretty new to Excel and was hoping y'all could give me some help with calculating certain dates with percentages. There are 3 different problems I'm trying to solve that are related to each other.

1. The first one I get. I need to find the Time Elapsed:

Start Date	1/1/2009
End Date	12/31/2009
Current Date	6/1/2009
Time Elapsed	42%

<tbody>
</tbody>

I found this by =(Current Date - Start Date+1)/(End Date - Start Date +1) and out popped the answer 42%. OK!

2. This one I think I have right, but I can't get the formatting right. I need to find Current Date:

Time Elapsed	85%
Start Date	1/1/2009
End Date	12/31/2009
Current Date	11/5/1900

<tbody>
</tbody>

I found this by =(End Date - Start Date)*Time Elapsed. Which makes sense, right? First, you find how long the duration is and then find how much of that is 85%. In this case, since it's in dates, 85% through this year would be 11/5/2009. But I don't understand why Excel spits out 1900 instead of 2009! Why is it doing this and how do I fix it??


3. Last one I have the most trouble with. I need to find the start date:

Time Elapsed	35%
Current Date	8/31/2009
End Date	12/31/2009
Start Date	#######

<tbody>
</tbody>

I thought it'd be like this =(((End Date - Current Date)/.65)*.35) - Current Date ). I think that makes sense mathematically, right? First, you see how many days are left between the End Date and the Current Date, then dividing that by 65% will give you the whole date range, then multiplying that by 35% gives you the Time Elapsed, and then you walk that number back from the Current Date to give you the Start Date. That seems to make sense to me, but, obviously, I'm doing something wrong either math wise or formula wise.

If you could lend a hand that'd be fantastic. I'm not doing anything anymore complicated that writing out = and clicking on the cells, so I don't know if I should be using different functions to find this out but I feel like I shouldn't have to...? This is also in 2013.

Again, thanks!

 

[MushroomFace]

2. Start date + (End Date - Start Date)*Time Elapsed

 

[FDibbins]

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Fri 26 Feb 2016) is actually 42426

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So, 11/5/1900 is actually 310 days, but is it formatted ad Date, so excel is trying to show what the date would be 310 dates from 0/0/1900

Format that as General, and you will have a number you will recognize

 

[jtakw]

Hi,

Here you go, I assumed you added 1 to your first formula to include the first day, so the rest of the formulas does the same:

Excel 2010
	A	B	C	D	E	F	G	H
1	Start Date	1/1/2009		Time Elapsed	85%		Time Elapsed	35%
2	End Date	12/31/2009		Start Date	1/1/2009		Current Date	8/31/2009
3	Current Date	6/1/2009		End Date	12/31/2009		End Date	12/31/2009
4	Time Elapsed	42%		Current Date	11/5/2009		Start Date	6/25/2009
Sheet1

Cell Formulas
Range	Formula
B4	=(B3-B1+1)/(B2-B1+1)
E4	=E2+(E3-E2)*E1-1
H4	=H3-(H3-H2)/(1-H1)-1

 

[dohraemon]

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Fri 26 Feb 2016) is actually 42426

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So, 11/5/1900 is actually 310 days, but is it formatted ad Date, so excel is trying to show what the date would be 310 dates from 0/0/1900

Format that as General, and you will have a number you will recognize

Thank you very much! I didn't know that, but knowing that it does make sense why my year was so off.

Hi,

Here you go, I assumed you added 1 to your first formula to include the first day, so the rest of the formulas does the same:

Excel 2010

	A	B	C	D	E	F	G	H
1	Start Date	1/1/2009		Time Elapsed	85%		Time Elapsed	35%
2	End Date	12/31/2009		Start Date	1/1/2009		Current Date	8/31/2009
3	Current Date	6/1/2009		End Date	12/31/2009		End Date	12/31/2009
4	Time Elapsed	42%		Current Date	11/5/2009		Start Date	6/25/2009

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B4 =(B3-B1+1)/(B2-B1+1) E4 =E2+(E3-E2)*E1-1 H4 =H3-(H3-H2)/(1-H1)-1 <tbody> </tbody>

<tbody>
</tbody>

Hey, wow you're good at this. Your formulas come out with the perfect answers, but do you mind if you could walk me through your work a little bit? You're right that I did add +1 to the Start Date to include it.

E4: I'm guessing that the E2 is there so it knows what date to add on from, but I don't understand why you put -1 at the end?

H4: Again, the H3 is there so it knows what date to backtrack from, then (H3-H2) gives you how many days are left divided by .65, which is given by (1-H1)....but again I'm lost as to why there's a -1 at the end

I get that you'd add 1 because when you're counting the days, you count off from the first day and end up not really counting it. But in the other 2, E4 and H4, wouldn't that still hold the same? That you're counting backwards from one date, to another date, so you're going to be losing that first day that you're counting off from? And shouldn't that mean it's a +1 instead of -1? Man I feel like an idiot or I'm overthinking this way too much.

Thanks again though and any further clarification would be much appreciated!

 

[dohraemon]

Sorry, and in E4 why is it that you don't add a +1 to the E2 for it to be (E3-E2+1) to count the first day?

 

[jtakw]

Thanks again though and any further clarification would be much appreciated!

Hi,

You're welcome, welcome to the forum.

E4 Formula:
E3-E2 gives the total number of days in between (days, a number), multiplied by E1 (85%) gives 85% of the number of days resulted from E3-E2, add this # of days to E2 (Start Date), then you get the Current Date, -1 (not +1) to include the first day because we worked backwards.

Same logic with H4 formula, if you want me to explain this also, let me know.

 

[dohraemon]

Oooohhhh I see now! Wow that totally makes sense and it was so simple. I couldn't get out of simply thinking about the dates in a forward linear fashion, but makes sense because you're working backwards and then need to -1 to include the first day, not +1 to add a day at the end.

Thank you very much for your help and patience!

 

[jtakw]

You're very welcome.