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!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,333
Office Version
  1. 2016
Platform
  1. Windows
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:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Start Date</td><td style="text-align: right;;">1/1/2009</td><td style="text-align: right;;"></td><td style=";">Time Elapsed</td><td style="text-align: right;;">85%</td><td style="text-align: right;;"></td><td style=";">Time Elapsed</td><td style="text-align: right;;">35%</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">End Date</td><td style="text-align: right;;">12/31/2009</td><td style="text-align: right;;"></td><td style=";">Start Date</td><td style="text-align: right;;">1/1/2009</td><td style="text-align: right;;"></td><td style=";">Current Date</td><td style="text-align: right;;">8/31/2009</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Current Date</td><td style="text-align: right;;">6/1/2009</td><td style="text-align: right;;"></td><td style=";">End Date</td><td style="text-align: right;;">12/31/2009</td><td style="text-align: right;;"></td><td style=";">End Date</td><td style="text-align: right;;">12/31/2009</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Time Elapsed</td><td style="text-align: right;;">42%</td><td style="text-align: right;;"></td><td style=";">Current Date</td><td style="text-align: right;;">11/5/2009</td><td style="text-align: right;;"></td><td style=";">Start Date</td><td style="text-align: right;;">6/25/2009</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=(<font color="Blue">B3-B1+1</font>)/(<font color="Blue">B2-B1+1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E4</th><td style="text-align:left">=E2+(<font color="Blue">E3-E2</font>)*E1-1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H4</th><td style="text-align:left">=H3-(<font color="Blue">H3-H2</font>)/(<font color="Blue">1-H1</font>)-1</td></tr></tbody></table></td></tr></table><br />
 

dohraemon

New Member
Joined
Feb 26, 2016
Messages
4

ADVERTISEMENT

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!
 

dohraemon

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

Well-known Member
Joined
Jun 29, 2014
Messages
5,333
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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:

dohraemon

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

Well-known Member
Joined
Jun 29, 2014
Messages
5,333
Office Version
  1. 2016
Platform
  1. Windows
You're very welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

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
Top