VBA to find the day number of the year from today's date?

danbates

Board Regular
Joined
Oct 8, 2017
Messages
234
Hi,

Please can someone help me?

I would like a VBA code that can find the day number of the year based from the current date.

Example: today is the 05/10/2019 so I would like the code to return 278. So tomorrow would return 279 and so on.

I have the date in cell T3 if that helps any?

Any help would be appreciated.

Thanks

Dan
 

danbates

Board Regular
Joined
Oct 8, 2017
Messages
234
Perfect thank you.

Can I ask, can your code be modified so it can add the following please?

I would like a 9 before the 278 and then 1092 afterwards.

Is that possible?

Thanks again

Dan
 

danbates

Board Regular
Joined
Oct 8, 2017
Messages
234
Perfect, thank you.
 

danbates

Board Regular
Joined
Oct 8, 2017
Messages
234
Hi Rick,

I did, but when AlphaFrog kindly supplied me with a formula I just started recording a macro. I entered the formula in my cell and then I pressed F2 and then enter and that gave me the formula in VBA.

Thanks

Dan
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,135
The day number is formatted as three digits; 001, 002, ...365

You could change that to suit.

Declare dn as String if you want a text result, or Long if you want a numeric value.

Code:
dn = "9" & Format(Date - DateSerial(Year(Date), 1, 0), "000") & "1092"
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,393
Office Version
2010
Platform
Windows
Code:
dn = "9" & Format(Date - DateSerial(Year(Date), 1, 0), "000") & "1092"
You can eliminate the concatenations by moving those numbers into the format pattern taking care to put a backslash in front of any 0's that are to remain constant within the pattern...
Code:
dn = Format(Date - DateSerial(Year(Date), 1, 0), "90001\092")
 
Last edited:

Forum statistics

Threads
1,077,614
Messages
5,335,253
Members
399,009
Latest member
twcaddell

Some videos you may like

This Week's Hot Topics

Top