Learn how to a build a cloud-first strategyRegister Now

x
Solved

Decimal Year to Normal Year Format Change

Posted on 2009-02-24
Medium Priority
802 Views
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.

0
Question by:Student_101
• 9
• 3
• 3

Author Comment

ID: 23723976
File now attached...
Decimal-year-conversion.xls
0

LVL 58

Expert Comment

ID: 23725710
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

Author Comment

ID: 23725744
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

Author Comment

ID: 23725861
The conversion from Decimal Year to General Date is the same as the universal method of conversion from decimal to general date.
0

LVL 22

Accepted Solution

danaseaman earned 1840 total points
ID: 23725955
Try this:

``````Option Explicit

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
``````
0

LVL 58

Expert Comment

ID: 23725969
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

Author Comment

ID: 23726066
danaseaman,

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.
0

LVL 22

Expert Comment

ID: 23726083
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

Author Comment

ID: 23726090
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

Author Comment

ID: 23726103
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

LVL 58

Assisted Solution

harfang earned 160 total points
ID: 23726105
danaseaman understood faster what you need. Attached is your Excel file with similar calculations.

(Â°vÂ°)
Decimal-year-conversion.xls
0

Author Comment

ID: 23726158
Thanks harfang

I will accept it as the assisted solution :)
0

Author Comment

ID: 23726280
danaseaman,

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

Thanks for your help, danaseaman and Harfang
0

LVL 22

Expert Comment

ID: 23726303
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

Author Comment

ID: 23726329
Yes danaseaman, your function takes care of the leap year.

Thanks a lot !
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
Suggested Courses
Course of the Month21 days, 1 hour left to enroll