Formatting TIME IN mm,ss format
Hello everyone,
I have a file in which I used to enter the speeds in cell E3.
If the value is less than 0, E3<0, then the value is considered as time.
So for time I enter -10 for 10 minutes.
In some cases I need to enter 10 minutes and 42 seconds.
I am wondering whether I can enter time in MM,SS format.
If its 10 min and 42 seconds I should get the result as 10+(42/60)=10+.70 = 10.7 minutes in F3.
Can someone help me in achieving this.
Please.
Thanks in advance.
so what are you actually entering in the cell? and where to you want the result to be?
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hello Martin,
Am entering the values in cell E3.
If its +30 its considered as speed and if its -10 its considered as 10 minutes.
If its -10,30 then it should be considered as 10 minutes and 30 seconds and the result 10.5 minutes should be displayed in cell F3.
Hope this clears you.
so what does 1 min 1 second look like? is it 01,01 or 1,1
It should look like 01,01.
sory to be a pain but 1 second looks like what ?
1 minute looks like what
10mins one second looks like?
attach a sample with all the possibilities
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Re: Formatting TIME IN mm,ss format
File attached with explanations.
Thanks in advance.
test.xlsx
ok but its not consistent
-1.00
(-)1,01
why is that not
-1,01
(-)1,01 - I tried inputting -1,01 but it resulted in an error.
Don't know what is preventing me from inputting this.
owhat are settings for date and what is your seperator set to in regional settings
The separator in regional settings is "."
For example:
If the value is cell A1 -01,23
then it should consider it as 1 minute and 23 seconds.
after that in cell B2 the answer should be 1+(23/60) = 1+38333333 = 1.3833333 minutes
What are the modifications to be done.
please explain and help me.
Formula:
Please Login or Register to view this content.
you will have to make sure cell is formatted text BEFORE entering that(-)1,01 - I tried inputting -1,01 but it resulted in an error.
Don't know what is preventing me from inputting this.
Thank you so much Martin.
This is working as expected.
But when I enter 36 in cell A1 the value in cell B1 is also showing 36 but with a error saying that the number is formatted as text.
Is it possible to have:
If the cell A1 is less than 0 then it should be formatted as TEXT and if it is greater than 0 then it should be formatted as number.
Could this be done?
its just warning that number in col a is text problem just turn of the error message col b will be a real number
Ok.
Will do that.
Thanks for your all the help.
Your solution helped me a lot.
Thanks again.
Hi,
There is a mistake in the formula.
Its calculating the formula for all the values.
I need the formula to be applied only if the A1 is less than 0.
Even If I enter 25.42 its showing 0.70 in the result. I need the result to be 25.42
Formula:Please Login or Register to view this content.
Hello Martin,
There is a error again.
If the value of A1<0 and is separated by commas then this formula is working fine.
If the value of A1<0 and not separated by commas also then its again dividing the fraction and displaying the result.
I need the formula to be implemented only when the value of A1 is <0 and separated by comma.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks