Agilent BenchVue -> Excel: Changing time format

[TimNJ]

Hi all,

I'm not exactly sure if this is the right place to ask, but perhaps someone might have an idea, maybe if you have a good deal of experience in Excel.

So I had BenchVue do some datalogging via a 34401A bench multimeter. I was able to easily export the data to Excel, but now I want to be able to graph it so that that the timescsale is in seconds (or maybe milliseconds). Problem is: It's formatted in as year-day-month, hour-minute-seconds. I've been going through the time formatting options on Excel and I can't seem to get it in seconds alone. Any ideas? Am I overlooking something?

Thank you.

[n45048]

I assume that format of Column 'E' is "text". You will need to split the contents of the cell into two distinct columns before you can really start working with it. It seems a bit silly that BenchVue doesn't do this for you.

Assuming that the data mask doesn't change (i.e.: Values are always yyyy-mm-dd hh:mm:ss.xxx) you can use the following formula in a new column to extract the date from your original data and display it in the correct order:

=DATE(LEFT(E2,4),MID(E2,6,2),MID(E2,9,2))

As for the time, use this formula in another column:

=RIGHT(E2,12) or =MID(E2,12,8) if you just want hh:mm:ss

It will mean though you have two sets of date/time, but you can always hide column E if you don't want to see it.

[apelly]

Excel times are stored as (days since some year).(seconds since midnight)

You can see this if you format a date as a number. For a time you just want the bit after the decimal. Once you know this it becomes much easier to work with time data.

Good luck.

[TimNJ]

Thank you all! I was going to ask more questions but I figured it out. For anyone else, basically you can convert the date into a long number like 41949.6813119213, for example. The non fractional part is the date . The decimal part is the time expressed as a fraction of a 24 hour day. (thanks apelly) So if you multiply that by the amount of seconds in a day (86400), you get the seconds (and milliseconds) value for each sample. If you then subtract off the entire first sample value, each sample will be the difference in time since the start, with initial t=0. Make sure you format all cells as numbers and give yourself ample decimal places. I was up to 15 on some.

Thanks for your help everyone.

[Zero999]

How about copying it to a text editor, saving it and importing it as text? I don't know about Excel but OpenOffice's spreadsheet enables you to convert space separated text into two separate columns.