Convert 5 digit numbers to date

Posted on 2016-08-26
Medium Priority
Last Modified: 2016-09-12
I have a text file with records like this.

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:


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?
Question by:NVIT
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
LVL 21

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.
LVL 12

Assisted Solution

tel2 earned 600 total points
ID: 41772673

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:
and format B10 as a time.  So, I'm not using any offset there...yet.
LVL 25

Assisted Solution

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


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.


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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

LVL 29

Accepted Solution

Olaf Doschke earned 1400 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.

Expert Comment

ID: 41772697
http://www.epochconverter.com/ has some methods for VBScript that may be relevant
LVL 25

Author Comment

ID: 41772705

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

Expert Comment

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


Years      31556926
Month      2629743
Days      86400
Hours      3600
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.
LVL 23

Expert Comment

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

Author Closing Comment

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.

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Invest in your employees with these five simple steps to improve employee engagement and retention.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

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