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!
- Follow via:
- RSS
- Email Alert
Question
0
Votes
Answers (2)
0
Votes
I probably can't help, but two questions come to mind
1. Are you working in Excel, Works, or something else? if the last, what?
2. How did the date format come to change?
2. How did the date format come to change?
12th Mar
Replies
HI,
1. Yes, excel 2013 (I should have put that before!, sorry)
2. Original file format is .csv
1. Yes, excel 2013 (I should have put that before!, sorry)
2. Original file format is .csv
chrispx001
13th Mar
0
Votes
Just forcing a display format
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
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
Updated - 12th Mar
Replies
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
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
chrispx001
13th Mar
Why don't you just wrap the entire function in a left({function},len(B1)-4)?
phil@...
13th Mar
