| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time in Minutes and seconds
I have times entered a minutes and seconds - 3.08 being 3 minutes and 8
seconds. I want to subtract 2.28 which would be 40 seconds, but obviously decimals returns 0.80. How do I get this to show 40? Thanks Rick |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time in Minutes and seconds
One way:
A1: 3.08 A2: 2.28 A3: =(SUBSTITUTE(A1,".",":") - SUBSTITUTE(A2,".",":")) * 1440 In article , Rick wrote: I have times entered a minutes and seconds - 3.08 being 3 minutes and 8 seconds. I want to subtract 2.28 which would be 40 seconds, but obviously decimals returns 0.80. How do I get this to show 40? Thanks Rick |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time in Minutes and seconds
Enter with a colon instead of a full stop.
3:08, not 3.08 -- David Biddulph "Rick" wrote in message ... I have times entered a minutes and seconds - 3.08 being 3 minutes and 8 seconds. I want to subtract 2.28 which would be 40 seconds, but obviously decimals returns 0.80. How do I get this to show 40? Thanks Rick |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time in Minutes and seconds
Thanks, it's long but it works. Except where one of the times is 2.00.
One example has 2.05 - 2.00 and gives, -2755. I am guessing it isn't recognising the .00. And obvioulsy the answer isn't working when the higher time is a full minute either. Any suggestions? Thanks again. Rick "JE McGimpsey" wrote: One way: A1: 3.08 A2: 2.28 A3: =(SUBSTITUTE(A1,".",":") - SUBSTITUTE(A2,".",":")) * 1440 In article , Rick wrote: I have times entered a minutes and seconds - 3.08 being 3 minutes and 8 seconds. I want to subtract 2.28 which would be 40 seconds, but obviously decimals returns 0.80. How do I get this to show 40? Thanks Rick |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time in Minutes and seconds
One way is like this:
=(TIME(0,INT(A1),MOD(A1,1)*100)- TIME(0,INT(B1),MOD(B1,1)*100))*60*60*24 assuming the larger time is in A1 and the smaller time in B1. Hope this helps. Pete On Nov 4, 8:31*am, Rick wrote: I have times entered a minutes and seconds - 3.08 being 3 minutes and 8 seconds. I want to subtract 2.28 which would be 40 seconds, but obviously decimals returns 0.80. How do I get this to show 40? Thanks Rick |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time in Minutes and seconds
Rick,
This is a great example of "I wouldn't start from here if I was you" It's far easier to enter times correctly and in a format Excel understands in the first place and I would recommend this for future. However you are where you are. Try this for subtracting A1-A2 Format as mm.ss =(INT(A1)/1440)+TIME(0,0,MOD(A1,INT(A1))*100)-((INT(A2)/1440)+TIME(0,0,MOD(A2,INT(A2))*100)) Mike "Rick" wrote: Thanks, it's long but it works. Except where one of the times is 2.00. One example has 2.05 - 2.00 and gives, -2755. I am guessing it isn't recognising the .00. And obvioulsy the answer isn't working when the higher time is a full minute either. Any suggestions? Thanks again. Rick "JE McGimpsey" wrote: One way: A1: 3.08 A2: 2.28 A3: =(SUBSTITUTE(A1,".",":") - SUBSTITUTE(A2,".",":")) * 1440 In article , Rick wrote: I have times entered a minutes and seconds - 3.08 being 3 minutes and 8 seconds. I want to subtract 2.28 which would be 40 seconds, but obviously decimals returns 0.80. How do I get this to show 40? Thanks Rick |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time in Minutes and seconds
Sorry format as [ss]
"Mike H" wrote: Rick, This is a great example of "I wouldn't start from here if I was you" It's far easier to enter times correctly and in a format Excel understands in the first place and I would recommend this for future. However you are where you are. Try this for subtracting A1-A2 Format as mm.ss =(INT(A1)/1440)+TIME(0,0,MOD(A1,INT(A1))*100)-((INT(A2)/1440)+TIME(0,0,MOD(A2,INT(A2))*100)) Mike "Rick" wrote: Thanks, it's long but it works. Except where one of the times is 2.00. One example has 2.05 - 2.00 and gives, -2755. I am guessing it isn't recognising the .00. And obvioulsy the answer isn't working when the higher time is a full minute either. Any suggestions? Thanks again. Rick "JE McGimpsey" wrote: One way: A1: 3.08 A2: 2.28 A3: =(SUBSTITUTE(A1,".",":") - SUBSTITUTE(A2,".",":")) * 1440 In article , Rick wrote: I have times entered a minutes and seconds - 3.08 being 3 minutes and 8 seconds. I want to subtract 2.28 which would be 40 seconds, but obviously decimals returns 0.80. How do I get this to show 40? Thanks Rick |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time in Minutes and seconds
=(IF(ISNUMBER(SEARCH(".",A1)),SUBSTITUTE(A1,".",": "),A1&":00") -
IF(ISNUMBER(SEARCH(".",A2)),SUBSTITUTE(A2,".",":") ,A2&":00")) * 1440 but better to put the data in with a colon in the first place. -- David Biddulph "Rick" wrote in message ... Thanks, it's long but it works. Except where one of the times is 2.00. One example has 2.05 - 2.00 and gives, -2755. I am guessing it isn't recognising the .00. And obvioulsy the answer isn't working when the higher time is a full minute either. Any suggestions? Thanks again. Rick "JE McGimpsey" wrote: One way: A1: 3.08 A2: 2.28 A3: =(SUBSTITUTE(A1,".",":") - SUBSTITUTE(A2,".",":")) * 1440 In article , Rick wrote: I have times entered a minutes and seconds - 3.08 being 3 minutes and 8 seconds. I want to subtract 2.28 which would be 40 seconds, but obviously decimals returns 0.80. How do I get this to show 40? Thanks Rick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I change a minutes and seconds as a 2400 time to seconds? | Excel Worksheet Functions | |||
| how can I add time that I entered in only minutes and seconds | Excel Discussion (Misc queries) | |||
| time in minutes and seconds and .00 | Excel Worksheet Functions | |||
| Time with minutes and seconds | Excel Discussion (Misc queries) | |||
| Convert "Time Interval" in "hours : minutes : seconds" to seconds | New Users to Excel | |||
Linear Mode
