View Full Version : [SOLVED:] VBA MACRO CODE FOR GENERATING DAYS NUMBERING
Dharani
08-20-2013, 02:02 AM
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
08-20-2013, 02:34 AM
A simple way could be using formula's, see attached.
Dharani
08-20-2013, 08:25 PM
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
08-21-2013, 02:37 AM
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
08-21-2013, 05:15 AM
Or even simpler:
=B3-date(Year(B3),1,1)+1
Kenneth Hobs
08-21-2013, 06:15 AM
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
08-21-2013, 06:30 AM
Darn, this thread is killing me, so many mistakes :-)
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)
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.
@SamT
the nth day in a year in VBA:
format(date,"y")
Paul_Hossler
08-21-2013, 07:09 PM
Darn, this thread is killing me, so many mistakes :-)
Do like I do and blame the keyboard :devil2:
Paul
Dharani
08-25-2013, 10:46 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.