Hello,
I have a problem for convert my time cell in second.
I have time data in this format:
mm’ss’’00
This time format is not standard that you can see. It’s that why I have a problem to convert the time cell in second.
I tried create a custom cell with this format. It’s not work.
I join a file example for illustrate my demand.
Thank for your help.
PS: I don’t know if I post in good section (may be I need a macro…)
Try this assuming the original time will never contain Hours (only minutes seconds and milliseconds)
=("0:"&SUBSTITUTE(SUBSTITUTE(F6,"''","."),"'",":"))*86400
Try this and format as general:
=LEFT(F6,SEARCH("'",F6)-1)*60+MID(F6,SEARCH("'",F6)+1,2)+RIGHT(F6,2)/100
Quang PT
Here's another one in G6:
=(LEFT(F6,FIND(CHAR(39),F6)-1)&":"&SUBSTITUTE(MID(F6,FIND(CHAR(39),F6)+1,2),CHAR(39),"")&"."&RIGHT(F6,LEN(F6)-FIND(CHAR(39)&CHAR(39),F6)-1))*24*60*60
then copy down. As you are in France, you might need to change the commas to semi-colons, so I've attached the file for you (I put the formula in J6).
Hope this helps.
Pete
Thank jonmo1 but i have a problem.
This formula is ok : =SUBSTITUE(SUBSTITUE(F6;"''";".");"'";":")
I have for F6 : 1:12,25
But when i try to use tje complete formula i have this "#valeur"
Last edited by Shami00; 02-10-2015 at 09:48 AM.
Yes, I kept getting that as I was developing my solution. It's because the '' in your examples to denote seconds are not the normal quotes character (code 34), but rather two separate apostrophes (code 39).
Hope this helps.
Pete
bebo021999,
With your forumula i have : 00'00,00
Pete_UK,
It's ok ! It's work !
Yes Peter i have the result that i would like with your solution.
Thank att all for your help.
Hello peter,
I have a little problem and i don't understand.
I have "#valeur" when i copy down your formula. Also when i click on enter button in the first cell, the result turns in "#valeur".
I change the commas to semi-colons and convert in my language the terms of the formulas with excel-translator.de website.
I search a solution but i don't find it. Maybe because i have excel 2007. I don't know.
Thank for your help.
Shami00.
There was no need to translate the functions, as that would have been done for you if you had opened the file that I attached. Can you attach the workbook again?
Pete
Also i tested with your file in post 4. I have the same problem.
Also when i copy and paste the formula in other cell i have the same problem that below.
When i use enter button i have this message :
Last edited by Shami00; 02-13-2015 at 10:19 AM.
I can't view image files on this forum - please attach your workbook instead (remove any sensitive data first).
Pete
You have no values in column F. If you want the formula cells to be blank in this case, then you should wrap the items in red below:
= IFERROR( ...existing_formula... ,"")
around your existing formula (but translated into French, and using a semicolon instead of a comma).
Hope this helps.
Pete
EDIT: I left the items in C3:D5 behind by mistake - you can delete these, as they have no effect on the formula.
Last edited by Pete_UK; 02-13-2015 at 12:52 PM.
It's Ok Pete.
This thread is solved for me.
Thank a lot
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks