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

danbates

Board Regular
Joined
Oct 8, 2017
Messages
238
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
238
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
238
Perfect, thank you.
 

danbates

Board Regular
Joined
Oct 8, 2017
Messages
238
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,136
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,426
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,078,442
Messages
5,340,305
Members
399,366
Latest member
ahmed elsaid

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top