Questions

Calculating duration with strange date / time format

+
0 Votes
Locked

Calculating duration with strange date / time format

chrispx001
I had a simple formula for working out a duration based on 2 columns of data. Each column had a date and a time in it in this format in it: "dd/mm/yyy hh:mm"

The formula was this: "(F2-E2)*24*60*60)"

Unfortunately now the date and time format has changed to be like this: "[01:02:2013][04:06:05:062]" so basicly there are now "[]" around the date and time and the time now includes seconds and milliseconds as well.

The formula now longer works and I have tried to create a custom number format in the hope it will recognise the new format, but I cant get it work (either to accept the new format or read the date/time correctly)

This is what i tried "["dd:mm:yyyy"]""["hh:mm:ss:000"]"

Does any one have any ideas how this can be achieved please? (The milliseconds are that important)

Thank you in advance for any help!
+
0 Votes
john.a.wills

1. Are you working in Excel, Works, or something else? if the last, what?
2. How did the date format come to change?

+
0 Votes
chrispx001

HI,

1. Yes, excel 2013 (I should have put that before!, sorry)
2. Original file format is .csv

+
0 Votes
Charles Bundy

Won't change the underlying data type. It sounds like the data type for those cells have changed from date/time to text. Can't be more specific w/o details. ( spreadsheet version, input data source, input/output example)

ADDENDUM:

This is some VBA code to check cell contents. Might help differentiate between datetime and text...

http://www.j-walk.com/ss/excel/tips/tip62.htm

+
0 Votes
chrispx001

Hi,

Thanks. I'm working in Excel 2013.. I have got a little further now..
I have successfully changed [01:02:2013][09:49:04:651] into 01/02/2013 09:49:04:651 by using the formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"[",""),"]","",2),"]"," ",1),":","/",2),":","/",1) BUT, i still need to strip off the last 4 digits/char (:651) which I think can be done by using the 'LEFT' option, but I cant figure out how to integrate it into the SUBSTITUTE formula above. Any ideas? Thanks

+
0 Votes
phil

Why don't you just wrap the entire function in a left({function},len(B1)-4)?