Convert 5 digit numbers to date

Posted on 2016-08-26
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
  • 3
  • 2
  • 2
  • +3
LVL 18

Expert Comment

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 150 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 24

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

LVL 29

Accepted Solution

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.

Expert Comment

ID: 41772697 has some methods for VBScript that may be relevant
LVL 24

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: 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 22

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 24

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

861 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