Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Decimal Year to Normal Year Format Change

Posted on 2009-02-24
15
Medium Priority
?
802 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:Student_101
  • 9
  • 3
  • 3
15 Comments
 

Author Comment

by:Student_101
ID: 23723976
File now attached...
Decimal-year-conversion.xls
0
 
LVL 58

Expert Comment

by:harfang
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

by:Student_101
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Student_101
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

by:
danaseaman earned 1840 total points
ID: 23725955
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
 
LVL 58

Expert Comment

by:harfang
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

by:Student_101
ID: 23726066
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
 
LVL 22

Expert Comment

by:danaseaman
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

by:Student_101
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

by:Student_101
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

by:harfang
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

by:Student_101
ID: 23726158
Thanks harfang

I will accept it as the assisted solution :)
0
 

Author Comment

by:Student_101
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

by:danaseaman
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

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

Thanks a lot !
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question