Decimal Year to Normal Year Format Change

Hi Experts,

I have a file in Excel that has the date in decimal year format.

I am Importing this file to Access, by Opening the file in an Excel Applicaiton, and then using the ExcelSheet.Range option, I add them to my Access Table with my ADODB recordset.

The problem now is the format.

I have Attached a file that has the initial format, and beside it is the final format that I want to achieve.

*** The wanted result is NOT CORRECT and is NOT CORRESPONDING to the initial data, it is only used to illustrate the format :)

The data follows a similar pattern throughout, and this is hourly data for about 10 years.

Thanks in advance.
Student_101Asked:
Who is Participating?
 
danaseamanConnect With a Mentor Commented:
Try this:

Option Explicit
 
Private Sub Form_Load()
   Debug.Print Format(DecimaltoDate(1998.071347), "dd-mmm-yyyy hh:mm")
   Debug.Print Format(DecimaltoDate(1998.071461), "dd-mmm-yyyy hh:mm")
   Debug.Print Format(DecimaltoDate(1998.071575), "dd-mmm-yyyy hh:mm")
   Debug.Print Format(DecimaltoDate(1998.071689), "dd-mmm-yyyy hh:mm")
End Sub
 
Public Function DecimaltoDate(ByVal dblValue As Double) As Date
   Dim Yr         As Integer
   Dim dblDays    As Double
   Dim DaysInYear As Long
   
   Yr = Int(dblValue)
   dblValue = dblValue - Yr
   
   If Month(DateSerial(Yr, 2, 29)) = 2 Then
      DaysInYear = 366 'Leap year
   Else
      DaysInYear = 365
   End If
   
 
   dblDays = DaysInYear * dblValue
   DecimaltoDate = DateAdd("d", Int(dblDays), DateSerial(Yr, 1, 1)) + dblDays - Int(dblDays)
 
End Function
 
Public Function DaysInYear(iYr As Integer) As Long
   If Month(DateSerial(iYr, 2, 29)) = 2 Then
      DaysInYear = 366 'Leap year
   Else
      DaysInYear = 365
   End If
End Function

Open in new window

0
 
Student_101Author Commented:
File now attached...
Decimal-year-conversion.xls
0
 
harfangCommented:
I think you need to explain what the "decimal year format" is, and how to interpret it. Let's look at the first row:

Decimal Year: 1998.07134703196
year: 1998
Month: 27
Hour Ending: 1

How do you read this? are the Month and Hour Ending values incorporated in the decimal portion? what's month 27 anyway?

Why didn't you show the same data in the new format? it would make a lot more sense, no?

(°v°)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Student_101Author Commented:
Hi Harfang,

I didn't have access to the converted data at this time, that's why I couldn't show it.

Sorry, the month is supposed to be day, my apologize

and Yes you are correct, the year, day, and hour ending are incorporated in the decimal portion.

The user graphs using the decimal year, and the days go from 1 to 365.
0
 
Student_101Author Commented:
The conversion from Decimal Year to General Date is the same as the universal method of conversion from decimal to general date.
0
 
harfangCommented:
This doesn't look right. Your data shows days 71, 72, and 73, They cannot all be on day 27 of any given month. Days 71 to 73 should be the 12th to the 14th of March, in 1998.

Please make the effort, and show us how you would interpret the decimal year 1998.07134703196. Year: 1998, day number: 71(?) meaning the 12th of March(?), time portion: 0.34703196(?) meaning 08:19:44(?), or time portion: starting and ending times 0.3196(?) to 0.3470(?) meaning 07:40:13(?) to 08:19:41(?)

I can write the functions in Excel or Access to convert the data, but you need to provide the full encoding; it's going to be hard to guess...

(°v°)
0
 
Student_101Author Commented:
danaseaman,

Your code works great.

I don't have the original results in front of me, but my years start from 1998, and the results also show the corresponding changes in hour and day correctly.

So I will go with your code.

Harfang,

Sorry I wasn't able to give you the correct details in terms of the encoding.
Thanks for your help :)
0
 
danaseamanCommented:
I pasted the vaues shown in Excel but if you use the stored value with greater precision the first four values output as:

27-Jan-1998 01:00
27-Jan-1998 02:00
27-Jan-1998 03:00
27-Jan-1998 04:00
0
 
Student_101Author Commented:
danaseaman,

Can you please check the file that I have attached earlier,

and the column called month is the day**

How can I get it to that format?

which also gives me the End Date and the EndTime?
0
 
Student_101Author Commented:
Yes, that's how I was able to interpret that your code works fine.

I meant that I don't have my actual conversions to check that with :)

But it looks right.

Can you help me with the Format wanted, so I can accept your solution and give you the points.
0
 
harfangConnect With a Mentor Commented:
danaseaman understood faster what you need. Attached is your Excel file with similar calculations.

(°v°)
Decimal-year-conversion.xls
0
 
Student_101Author Commented:
Thanks harfang

I will accept it as the assisted solution :)
0
 
Student_101Author Commented:
danaseaman,

Don't worry about the rest, ill take care of it :)

Thanks for your help, danaseaman and Harfang
0
 
danaseamanCommented:
For integer  day just use Debug.Print Day(DecimaltoDate(1998.07134703196)) or use Int Function in Excel. Note that if you are looking at historica data, you must check for leap year as shown in the Formula I provided.

0
 
Student_101Author Commented:
Yes danaseaman, your function takes care of the leap year.

Thanks a lot !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.