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:
<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:
<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:
<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!
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!