Calculate multiple DateDiffs

pretzel

New Member
Joined
Oct 2, 2014
Messages
18
Hello peeps,

Bear with me; I have no formal education to VBA and I am starting to pick up stuffs. My current project requires me to calculate a list of number of days between a list of dates and a fixed date. And I need to apply a fixed formula, which is exponential, into each of these derived values. For example:


Sub TryingOut()

Dim srtdate As Date, today As Date, years As Integer

'Calculate the number of years between dividend payout date and today
srtdate = Range("E7")
today = Sheets("Outlook").Range("C8")
years = DateDiff("yyyy", today, srtdate)

'The formula I would like to multiply against a list of values
Dim x As Long
x = Exp(-years)

End Sub

My list of dates occupies cell E7 to the very bottom. today is the fixed date. For each DateDiff, I would like to derive many x and multiply the x with the values in column F - my values also start from F7 all the way to the bottom.

How can I accomplish that? I googled around and noticed some users use Evaluate("F1:F" & "today"), something like that. Should I use loop?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Angga06

New Member
Joined
Dec 21, 2015
Messages
4
Greetings,

Can someone help me please. I'm trying to post a New Thread but i can't find it, so i reply on this thread, sorry about this.
Please help me out. I have a multiple date in cell F written like this apostrophe date space ('19-Nov-15 07-Dec-15 08-Dec-15 15-Dec-15).
May question is how can I calculate the number of days between last date 15-Dec-15 and today date / or now().

Thank you for your help in advance.


Best Regards,

Angga
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
I'm trying to post a New Thread but i can't find it

Hi, it's "+ post new thread" button at the top of this page:

Excel Questions

Here is one option for your question:

<b>Excel 2012</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 /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #333333;;">'19-Nov-15 07-Dec-15 08-Dec-15 15-Dec-15</td><td style="text-align: right;background-color: #FFFF00;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;;">'19-Nov-15 07-Dec-15 08-Dec-15 21-Dec-15</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #333333;;">'19-Nov-15 07-Dec-15 08-Dec-15 01-Jan-15</td><td style="text-align: right;;">354</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">B1</th><td style="text-align:left">=TODAY(<font color="Blue"></font>)-TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">A1," ",REPT(<font color="Purple">" ",10</font>)</font>),10</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Angga06

New Member
Joined
Dec 21, 2015
Messages
4
Hi, it's "+ post new thread" button at the top of this page:

Excel Questions

Here is one option for your question:

Excel 2012
AB
1'19-Nov-15 07-Dec-15 08-Dec-15 15-Dec-156
2'19-Nov-15 07-Dec-15 08-Dec-15 21-Dec-150
3'19-Nov-15 07-Dec-15 08-Dec-15 01-Jan-15354

<tbody>
</tbody>
Sheet1


Worksheet Formulas
CellFormula
B1=TODAY()-TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))

<tbody>
</tbody>

<tbody>
</tbody>




Hi FormR,

Thank you for this it works perfectly. You're brilliant. Hope you guys will continue to support and assist us always.

Just a follow up question please, can we exclude the weekend (2)days? and also it is ok to tell me what's the 10 means in the formula?
And again, I still don't see the + post new thread at the top button, can i screen shot and send it to you?

Thanks a lot.



Best Wishes,
Angga
 
Last edited:

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi, glad to help and welcome to the forum :)

Just a follow up question please, can we exclude the weekend (2)days?

Give this one a try to exclude the weekends:

<b>Excel 2012</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 /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">'19-Nov-15 07-Dec-15 08-Dec-15 15-Dec-15</td><td style="text-align: right;color: #333333;background-color: #FFFF00;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">'19-Nov-15 07-Dec-15 08-Dec-15 21-Dec-15</td><td style="text-align: right;color: #333333;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">'19-Nov-15 07-Dec-15 08-Dec-15 01-Jan-15</td><td style="text-align: right;color: #333333;;">252</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">B1</th><td style="text-align:left">=NETWORKDAYS(<font color="Blue">TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">A1," ",REPT(<font color="Teal">" ",10</font>)</font>),10</font>)</font>),TODAY(<font color="Red"></font>)</font>)-1</td></tr></tbody></table></td></tr></table><br />



And again, I still don't see the + post new thread at the top button, can i screen shot and send it to you?

Maybe you didn't notice the hyperlink in my response: The button should be visible at the top of this link:

Excel Questions
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Actually, if your dates are in a consistent format, then this is better:

<b>Excel 2012</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 /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">'19-Nov-15 07-Dec-15 08-Dec-15 15-Dec-15</td><td style="text-align: right;color: #333333;background-color: #FFFF00;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">'19-Nov-15 07-Dec-15 08-Dec-15 21-Dec-15</td><td style="text-align: right;color: #333333;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">'19-Nov-15 07-Dec-15 08-Dec-15 01-Jan-15</td><td style="text-align: right;color: #333333;;">252</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">B1</th><td style="text-align:left">=NETWORKDAYS(<font color="Blue">RIGHT(<font color="Red">A1,9</font>),TODAY(<font color="Red"></font>)</font>)-1</td></tr></tbody></table></td></tr></table><br />
 

Angga06

New Member
Joined
Dec 21, 2015
Messages
4
Hi FormR,

Thanks a lot. Both solution are perfect it works.

Merry Christmas & A Blessed New Year ahead! :)


Cheers,
Angga

 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

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