# Convert 5 digit numbers to date

I have a text file with records like this.
BackupDate,BackupTime,BackupTimeUTC
15013,18724,1241550548

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)

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?
LVL 26
###### Who is Participating?

Software DeveloperCommented:
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

Group Finance ManagerCommented:
Have you tried simply formatting as dates in Excel. Excel actually records dates as 5 digit numbers?

Attach an example file.
0

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

Author Commented:
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

Commented:
http://www.epochconverter.com/ has some methods for VBScript that may be relevant
0

Author Commented:
@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

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

Software DeveloperCommented:
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

Director of Information TechnologyCommented:
• Are you pulling this from SQL directly?
• What version of BE are you using?
• What is your goal with this data?
0

Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.