Calculate multiple DateDiffs

[pretzel]

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?

 

[Angga06]

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]

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:

Excel 2012
	A	B
1	'19-Nov-15 07-Dec-15 08-Dec-15 15-Dec-15	6
2	'19-Nov-15 07-Dec-15 08-Dec-15 21-Dec-15	0
3	'19-Nov-15 07-Dec-15 08-Dec-15 01-Jan-15	354
Sheet1

Cell Formulas
Range	Formula
B1	=TODAY()-TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))

 

[Angga06]

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

Excel Questions

Here is one option for your question:

Excel 2012

	A	B
1	'19-Nov-15 07-Dec-15 08-Dec-15 15-Dec-15	6
2	'19-Nov-15 07-Dec-15 08-Dec-15 21-Dec-15	0
3	'19-Nov-15 07-Dec-15 08-Dec-15 01-Jan-15	354

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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

 

[FormR]

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:

Excel 2012
	A	B
1	'19-Nov-15 07-Dec-15 08-Dec-15 15-Dec-15	4
2	'19-Nov-15 07-Dec-15 08-Dec-15 21-Dec-15	0
3	'19-Nov-15 07-Dec-15 08-Dec-15 01-Jan-15	252
Sheet1

Cell Formulas
Range	Formula
B1	=NETWORKDAYS(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10)),TODAY())-1

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]

Actually, if your dates are in a consistent format, then this is better:

Excel 2012
	A	B
1	'19-Nov-15 07-Dec-15 08-Dec-15 15-Dec-15	4
2	'19-Nov-15 07-Dec-15 08-Dec-15 21-Dec-15	0
3	'19-Nov-15 07-Dec-15 08-Dec-15 01-Jan-15	252
Sheet1

Cell Formulas
Range	Formula
B1	=NETWORKDAYS(RIGHT(A1,9),TODAY())-1

 

[Angga06]

Hi FormR,

Thanks a lot. Both solution are perfect it works.

Merry Christmas & A Blessed New Year ahead!


Cheers,
Angga

 

[Angga06]

**perfectly works.