VBA MACRO CODE FOR GENERATING DAYS NUMBERING

[Dharani]

Respected Sir / Madam,

I need VBA macro code for generating output like in which I need the days to be added depending on the leap year or not. For normal year I need 365 days numbering and for leap year I need 366 days numbering. I have done it manually for your easy understanding and attached the excel sheet with this thread for your kind reference and perusal.

Please oblige and do the needful.
Thanks & Regards,
Dharani.

[Jan Karel Pieterse]

A simple way could be using formula's, see attached.

[Dharani]

A simple way could be using formula's, see attached.

Dear Jan Karel,
Thank you for the quick response... Thank for the effort u made... But please can u explain me how this works...??? In this excel sheet, it is working fine.. ok but how do I do the same with other excel sheets... please reply me....

Thanks & Regards,
Dharani.

[Jan Karel Pieterse]

Looks like my solution is wrong. Row 369 clearly shows that.

A better formula is this one:

=IF(AND(MONTH(B3)=1,DAY(B3)=1),1,E2+1)
SO: If the current row is January first, the daynumber is one. Otherwise, add one to the cell above.

[Jan Karel Pieterse]

Or even simpler:
=B3-date(Year(B3),1,1)+1

[Kenneth Hobs]

From Jan's example workbook, I think he meant: =B3-date(Year(B3),1,1)+1

This works because the dates in B3 are dates incremented by 1 for each day.

Dharani, for a VBA solution, you should post an example showing the before and after result needed. Put all of your workbooks in the same folder so that a batch macro can change all at once. Obviously, your data needs to be structured so that the macro can update the files correctly. Ergo, my request for a simple structured example.

If you make your workbook cells use a date format of yyyy, then incrementing the dates as Jan did will make working with dates and getting a day number for that date easy as Jan demonstrated.

[Jan Karel Pieterse]

Darn, this thread is killing me, so many mistakes :-)

[snb]

Probably:

Sub M_snb()
Cells(1, 1).Resize(1200) = [index(date(1936,1,1)+row(1:1200) -date(year(date(1936,1,1)+row(1:1200)-1),1,1),)]
End Sub

or using a UDF:


Function F_day(c00)
F_day = Format(DateSerial(1936, 1, 1) + c00.Row - 1, "y")
End Function

In G1:
= F_day(A1)

[SamT]

Function NumDaysInAnyYear (DDate As Date) As Long
NumDaysInAnyYear = Date(Year(DDate) + 1, 1, 1) - Date(Year(DDate), 1, 1)
End Function
How it Works:
Year(DDate) + 1 is Year after DDate Year
Date(Year(DDate) + 1, 1, 1) is January 1 of following year
Date(Year(DDate), 1, 1) is January 1, DDate year




Function JulianDateAnyDate(DDate As Date) As Long
JulianDateAnyDate = DDate - Date(Year(DDate), 1, 0)
End Function
How it works:
The zeroth day of any month is the last day of the previous month.
Date(Year(DDate), 1, 0) is December 31, previous year.

[snb]

@SamT

the nth day in a year in VBA:


format(date,"y")

[Paul_Hossler]

Darn, this thread is killing me, so many mistakes :-)

Do like I do and blame the keyboard :devil2:

Paul

[Dharani]

Respected Sir/ Madam,
Thank you all people who helped me in this thread... and also thank you for the efforts you made...

Thanks & Regards,
Dharani.