create a single unique date/time code from 2 cells in excel

[legoman666]

I have a rather large Excel spreadsheet with electric load data from several houses. EG, I have kW, kVA, and PF (kilowatts, kilovolt amps, and power factor) information taken at 15 minute intervals for several months for ~12 houses. Problem is, when I try to graph the data, it gets all murky because of the way the date/time is formatted.

6/20/07 | 845
6/20/07 | 900
6/20/07 | 915
6/20/07 | 930
6/20/07 | 945
6/20/07 | 1000
6/20/07 | 1015
6/20/07 | 1030

How would I create a single cell with a unique date/time number so that the different days don't overlap when I graph them?

Here's a small snapshot of the spreadsheet so you can see how it's formatted.
http://img165.imageshack.us/img165/7642/helptl5.jpg

 

[TSDible]

The easiest way is going to be to add a numbering column before your date and possibly use that to plot your chart.

Is the second column your date time?

 

[TSDible]

Assuming that your date is in column A, and the time is in column B, try the following formula in column C to make a unique increasing number for each date time...

=(A1&B1)*1

 

[TSDible]

On second thought....

That example only works if you are only looking at one day....

To do multiple days, you can use the following:

=(A1&REPT(0,4-LEN(B1))&B1)*1

 

[mayest]

In Excel, a date is a date/time number. The digits to the left of the decimal point represent the day and the digits to the right represent the time of day. This is true even if you just type in 6/20/2007 (that would be 12:00 AM on 6/20/2007 and if you format it as a number you will see that it is 39,253.00). So, I think your problem is because you have separated the date and time.

Depending on how the data that you receive is formatted, you will probably need to use some of the date formulas to construct a proper date. You may need the Date(), DateValue(), Time(), and TimeValue() functions as well as using some string functions to build a proper date/time.

 

[legoman666]

Originally posted by: mayest
In Excel, a date is a date/time number. The digits to the left of the decimal point represent the day and the digits to the right represent the time of day. This is true even if you just type in 6/20/2007 (that would be 12:00 AM on 6/20/2007 and if you format it as a number you will see that it is 39,253.00). So, I think your problem is because you have separated the date and time.

Depending on how the data that you receive is formatted, you will probably need to use some of the date formulas to construct a proper date. You may need the Date(), DateValue(), Time(), and TimeValue() functions as well as using some string functions to build a proper date/time.

ah thanks. That worked. I converted the date cells to date format, then fiddled with the time cells to seperate them into 2 columns. Then I just added the two times together to get a unique number for each entry.

Thanks for the help you two!