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

dohraemon

New Member
Joined
Feb 26, 2016
Messages
4
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!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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
ABCDEFGH
1Start Date1/1/2009Time Elapsed85%Time Elapsed35%
2End Date12/31/2009Start Date1/1/2009Current Date8/31/2009
3Current Date6/1/2009End Date12/31/2009End Date12/31/2009
4Time Elapsed42%Current Date11/5/2009Start Date6/25/2009
Sheet1
Cell Formulas
RangeFormula
B4=(B3-B1+1)/(B2-B1+1)
E4=E2+(E3-E2)*E1-1
H4=H3-(H3-H2)/(1-H1)-1
 
Upvote 0
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
ABCDEFGH
1Start Date1/1/2009Time Elapsed85%Time Elapsed35%
2End Date12/31/2009Start Date1/1/2009Current Date8/31/2009
3Current Date6/1/2009End Date12/31/2009End Date12/31/2009
4Time Elapsed42%Current Date11/5/2009Start Date6/25/2009

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
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!
 
Upvote 0
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,216,106
Messages
6,128,863
Members
449,473
Latest member
soumyahalder4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top