Creating a Date/Time Stamp

[Buck1480]

Hello, I'm working on a large database (> 100,000 lines in Excel) and I would like to create a unique date/time stamp for each line of data. My data is set-up as follows:

ID Date Hour Minute Second Time
1 11/22/2008 11 00 00 11:00:00
1 11/22/2008 11 08 00 11:08:00
1 11/22/2008 11 16 00 11:16:00

Is there an easy way to create a date/time stamp like a UNIX output (1234567890)? I will use this value in my modeling procedure. Thank you very much!

 

[kpark91]

I'm not sure what you're really asking for since you've already gotten all the dates and times but
do you mean concatenation??

Then
use

=CONCATENATE(SUBSTITUTE(B1, "/", ""), C1, D1, E1)

assumed Date is in column B, Hour in colC, Minute in colD, Second in colE

 

[Buck1480]

Sorry for not being clear. I want to model temporal autocorrelation in my data set and I need to include some type of time value for the model to work properly. My statistics program doesn't recognize the concatenate function (combining date and time together). I want to create a numerical value that corresponds to the date/time for each line in Excel (ex. 11/22/2008 and 11:00:00 would be combined to form a numerical value such as: 1234567890 and each line in Excel would have a similar value but the date/time would change for each line).

Here's an explanation:

Date represents days since 1/1/1960 and time represent seconds since midnight or midnight of 1/1/1960 if specifying a datetime variable.

Thus the I will create a time stamp for each data point using both date and time to create a value.

Let me know if I'm still not clear.

Thanks!

 

[Gerald Higgins]

It looks as if the times you have are NOT times that Excel recognises, but instead you have numbers in separate cells representing the number of hours, minutes, seconds etc in the time.

You can combine these into a single cell, either in the way kpark91 suggested, or alternatively like this

=(C1*3600)+(D1*60)+E1

I've assumed the same cell references as kpark.
This will convert the time into the total number of seconds.
You could combine this with the date if you like.