First of all, I think there's some things we both need to understand.
There are "regional" settings in Excel that allow for some standard conventions.
For instance: in different parts of the world,
2 and 1/4 is represented as 2.25 or as 2,25
So, is your designation of 23,54 using a "," as a fractional representation?
or is it a text separator?
perhaps if you send me a sample file, I could answer my own question.
Next:
Excel considers "date" and "time" as a number.
The "date" is the number of days since 1/1/1900.
So, today, August 8, 2014 is 41859 days since January 1, 1900.
8:00am is 8/24 of a day, or .3333333
9:00am is 9/24 of a day, or .3750000
of course, at 12:00pm (noon), 1/2 of the day has passed, so the value is .50000
By storing this as a number, excel allows us to DISPLAY that number in any
manner we wish:
For instance, 7:15 am on August 8, 2014 can be shown as:
Friday, August 08, 2014
8/8/2014
8/8/14
08/08/14
8-Aug
8-Aug-14
August 8, 2014
8/8/14 7:15 AM
07:15:00
All are the same NUMBER, just DISPLAYED differently.
Excel will also make some "assumptions".
Like: if you enter a value in a cell like: 00:23:54
it will ASSUME that you're entering a "time" and will convert the VALUE to
0.0165972222222222
then, you can change the cell formatting to DISPLAY as mm:ss
and you get 23:54 in the cell, even though the VALUE is 0.0165972222222222
So, depending on what your number 23,54 represents, you need to decide how to
convert it to a number.
One way would be to use Excel's time() function.
=time(hh,mm,ss) allows you to insert the string portion of the value into the
function.
ASSUMING YOUR VALUE IS A TEXT STRING,
you need to "find" the ","
Let's say Cell A1 has 23,54
in Cell B1, you can use =find(",",A1) and determine that the "," is in location
3.
the =MID() function allows you to pull out a portion of the string
=MID(A1,1,2) will look at the value in cell A1, from the 1st character and pull
2 characters (one less than the location of the ","), or "23"
Now, since we don't KNOW that all your values will have a 2 digit minute value,
we need to plug in the FIND() function for the "2" value:
=MID(A1,1,FIND(",",A1)-1)
Now, to find the SECONDS, we need to START with the character AFTER the "," and
take the next 2 characters (since there will never be more than 59 seconds)
=FIND(",",A1)+1
using MID():
=MID(A1,FIND(",",A1)+1,2)
will give you the "seconds" portion.
So, using the =time() function and substituting the two =MID() functions:
=TIME(0,MID(A1,1,FIND(",",A1)-1),MID(A1,FIND(",",A1)+1,2))
Depending on your Excel default settings, the value displayed will probably be
in am/pm display:
12:23:AM (23 minutes, 54 seconds after midnight)
You can change the cell formatting to a "custom" format of "mm:ss"
and the time will be displayed as:
23:54
One of the largest benefits of Excel storing date/time as a number is that it
makes calculating "elapsed time" very simple.
subtracting any two date/time values will give you the number of days and
fraction of days between the two dates.
the difference between
Friday, August 07, 2014 7:15 AM
Friday, August 07, 2014 3:57 PM
is 0.362500 days, or
8:42
(which means I logged in at 7:15am and logged out at 3:57pm,
so I was at work for 8 hours and 42 minutes)
hope this helps.
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Lieke Van Klink <[email protected]>
>To: [email protected]
>Sent: Friday, August 8, 2014 6:00 AM
>Subject: $$Excel-Macros$$ need help!!!
>
>
>
>Hy guys!
>
>I am a student from holland, doing my thesis. and i need some help with excel
>(i never had to use it before, so have no clue on how it works, tried so much
>already and am still failing, regular sites do not help at all)
>I am trying to convert my time notation: min,sec ex. 23,54 into a mm:ss
>notation. but all i try Excel turns it into an hh:mm:ss notation as follows:
>23:54:00 where it is supposed to be 00:23:54
>How do i correct this? (NOTE first notation has comma! thats where the issue
>starts)
>
>the goal here is to convert the first notation: mm,sec into all seconds so for
>example: 4,33 becomes 4*60=240, 240+33=273 sec. (if there is an easier way of
>transforming that firat notation into omly seconds pleas telle me?!!!
>Im desperate, its for my thesis and I am so stuck on this.
>
>I hope someone of you is able to help me!!!
>PS: pleas do explain it in simple minds english, this is not my first language.
>
>If you can give met the notation that would be even better
>
>thanks in advance!!!
>
>
--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to [email protected].
>To post to this group, send email to [email protected].
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
