Solved

Convert 5 digit numbers to date

Posted on 2016-08-26
10
88 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
Comment Utility
Have you tried simply formatting as dates in Excel. Excel actually records dates as 5 digit numbers?

Attach an example file.
0
 
LVL 11

Assisted Solution

by:tel2
tel2 earned 150 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
http://www.epochconverter.com/ has some methods for VBScript that may be relevant
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 23

Author Comment

by:NVIT
Comment Utility
@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
Comment Utility
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
Comment Utility
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 21

Expert Comment

by:yo_bee
Comment Utility
  • 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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If your app took Google’s lash recently, here are the 5 most likely reasons.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

15 Experts available now in Live!

Get 1:1 Help Now