Solved

Convert 5 digit numbers to date

Posted on 2016-08-26
10
106 Views
Last Modified: 2016-09-12
I have a text file with records like this.
BackupDate,BackupTime,BackupTimeUTC
15013,18724,1241550548

Open in new window


The data came from Backup Exec catalogs.

I know (I think) how to convert the BackupTimeUTC field. Getting the dd/mm/yyyy is good enough for me. This seems to do it. I'm using excel to convert:

=A10/86400+DATE(1970,1,1)

Open in new window


Where A10=BackupTimeUTC=1241550548

Which shows as it should: 5/5/2009  7:09:08 PM

What I need to know is...
- How to convert the other fields, BackupDate and BackupTime? Those fields don't seem to indicate they are the same type as the BackupTimeUTC. If they were, wouldn't they just be concatenated, resulting in the same value? Anyway, what's the correct excel formula to convert them?

Or, maybe they are unrelated to BackupTimeUTC and are suppose to result in different values?
0
Comment
Question by:NVIT
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41772661
Have you tried simply formatting as dates in Excel. Excel actually records dates as 5 digit numbers?

Attach an example file.
0
 
LVL 12

Assisted Solution

by:tel2
tel2 earned 150 total points
ID: 41772673
Hi NVIT,

I don't know anything about Backup Exec catalogs, and unless you can find out some other way, you might have to do some testing to find out what dates & times those fields BackupDate & BackupTime correspond to.  From there, you can probably make them display correctly in Excel, adding offsets if required.

Assuming 18724 is seconds past midnight and is in cell B10, you could do this kind of thing to display it as a time in a different cell:
    =B10/86400
and format B10 as a time.  So, I'm not using any offset there...yet.
0
 
LVL 23

Assisted Solution

by:NVIT
NVIT earned 0 total points
ID: 41772682
Hi all. Thanks for your help.

@Roy,

I've tried formatting BackupDate and the year is way off. I don't remember exactly. I'm not at a computer now. I think it was 1944 or close to that. Very obvious and different.

@tel2

I'll try your example with BackupTime later. I'm not at a computer.

What about BackupDate, i.e. 15013? Would it also be 15013/86400
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 350 total points
ID: 41772695
86400 is the number of seconds of a day 24*60*60 = 86400, that's why no, I won't assume the BackupDate number is related to seconds, it's much too low for that. It may be about days since a certain start date, like 1/1/1970 is for the UTC datetime, it may be another date.

BackupTime could certainly be seconds since midnight, for that assumption 18724 would translate to 05:12:04 AM. Could that be right? It might be another formula needed, there is no ultimate formula you can apply to anything, why would there be?

You typically can only judge and find out what things mean by looking at something you know the real value of and also by knowing more than just a single example. I could also ask you what 46 is, and it might be my age or my (european) shoe size, it might even be both. You know 18724 is meant as time, it is in the range of 0..86400, but if you look at further values , what range do they cover? If they only vary from 0 to 20000 it's likely meaniong something else but seconds since midnight.

In regard to 15013 as a date, if I would assume it should be 5/5/2009, too, then making the assumption it is a number of days, I would compute 5/5/2009-15013 to find out a reference date. This results in 03/28/1968 and unless that is the birthday of the programmer of Backup Exec or any other sepcial date, I don't assume this is the correct assumption. Typical ultimo dates should rather be like 1/1 of some year. Since the time would also not match BackupTimeUTC it might be another datetime, the names don't make sense then, though.

You ask too much here and give too little information to know something, unless someone knows Backup Exec catalogs specifications. Why not ask the vendor about that file specs?

Bye, Olaf.
0
 
LVL 5

Expert Comment

by:mbkitmgr
ID: 41772697
http://www.epochconverter.com/ has some methods for VBScript that may be relevant
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 23

Author Comment

by:NVIT
ID: 41772705
@Olaf

Thanks for your input. I'll look at a bigger sample, i.e. records of BackupDate and BackupTime and see what range their values fall within
0
 
LVL 5

Expert Comment

by:mbkitmgr
ID: 41772706
I'm playing in excel now and have extracted the year
A1 Has the UTC Number 1472285094

=1970+INT(A1/31556926)

Years      31556926
Month      2629743
Days      86400
Hours      3600
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41772707
mbkitmgr I think that is simply nuixtimestamp definition, number of seconds since 1/1/1970 midnight. Since 31556926 is about the seconds of a year your formula would be right in most cases but some around new years eve and new year. Many programming languages simply allow you to compute seconds or other differences between two datetimes and also vice versa add some time interval to a datetime to compute another datetime, I wouldn't use that formula for the year only when you can have the exact datetime of it,including accounting of leap days, etc.

Someone else already mentioned dates are stored as a number in excel cells, too, and I found a formula to convert unixtimestamp to excel date as: value / 86400 + 25569.

The meaning of 25569 reveals, if you subtract it from 1/1/1970 and get to 12/30/1899. A bit unusual, it would mean if you write 2 into a cell and then specify the cell should be a date, that should convert to 1/1/1900

Bye, Olaf.

PS: Found this reference: http://excelhowto.info/wp/tag/date/ It states a numeric date simply is number of days since 31st December of 1899, so it counts days starting with 1=1/1/1900, then the formula for unix timestamp to excel conversion is off by one day, or I miscalculated. Anyway, 31/12/1899 + 15013 would be 7/2/1941 +/- one day, doesn't make sense, so 15013 is not an excel day number, well, it is - for that 1941 date - but if the backup is only from a few years ago 1941 doesn't make sense.
0
 
LVL 22

Expert Comment

by:yo_bee
ID: 41772892
  • Are you pulling this from SQL directly?
  • What version of BE are you using?
  • What is your goal with this data?
0
 
LVL 23

Author Closing Comment

by:NVIT
ID: 41795323
Luckily, I ended up getting the time from a different file. I didn't have time to figure out the time from this certain file. I'm giving points because it helped me to think more about it.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

896 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now