Formatting Time (MM:SS) without using hours and dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1514990%22%20slang%3D%22en-US%22%3EFormatting%20Time%20(MM%3ASS)%20without%20using%20hours%20and%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514990%22%20slang%3D%22en-US%22%3E%3CP%3EI%20work%20in%20manufacturing%2C%20and%20am%20trying%20to%20add%20to%20excel%20a%20minutes%20and%20second%20column%20for%20showing%20how%20long%20it%20takes%20to%20make%20each%20part%20for%20a%20full%20item.%20I%20am%20needing%20to%20be%20able%20to%20add%20up%20all%20of%20the%20minutes%20and%20seconds%20as%20well%20without%20them%20being%20in%20relation%20to%20a%20date%20or%20the%2024%20or%2012%20hour%20clock..%20if%20that%20makes%20sense.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20the%20mm%3Ass%20and%20mm%3Ass.0%20but%20nothing%20works.%20I%20am%20not%20wanting%20to%20have%20to%20input%20hours%20as%20I%20don't%20need%20them.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1514990%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1515166%22%20slang%3D%22de-DE%22%3ESubject%3A%20Formatting%20Time%20(MM%3ASS)%20without%20using%20hours%20and%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1515166%22%20slang%3D%22de-DE%22%3ESelect%20lines%20where%20you%20write%20the%20time%2C%20right%20mouse%20button%2C%20select%20format%20lines%2C%20click%20on%20numbers%2C%20click%20on%20the%20date%20and%20there%20you%20can%20choose%20any%20time%20format%20you%20want.%20Then%20you%20can%20enter%20your%20time%20in%20the%20selected%20lines%20and%20add%20up%20as%20you%20like.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20With%20User%20define%20there%20are%20also%20some%20ways%20to%20play%20with%20time.%20%3CBR%20%2F%3E%20User%20defined%3A%20h%3A%20mm%3B%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20If%20you%20want%20something%20more%20specific%20you%20have%20to%20upload%20a%20file%20so%20that%20we%20can%20understand%20your%20plan.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20*I%20know%20that%20I%20know%20nothing%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1515182%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20Time%20(MM%3ASS)%20without%20using%20hours%20and%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1515182%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725055%22%20target%3D%22_blank%22%3E%40cpowelske%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20far%20as%20formatting%2C%20I%20think%20%5Bm%5D%3Ass%20is%20what%20you're%20looking%20for.%20But%2C%20it%20appears%20excel%20still%20requires%20you%20to%20key%20the%20hour%20when%20you%20enter%20the%20data.%20So%2C%20you'd%20have%20to%20key%20a%200%20placeholder%20(0%3A20%3A36%20for%2020%20minutes%2C%2036%20seconds).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20extra%20keying%20of%20the%20leading%200%20is%20an%20issue%20from%20a%20data%20entry%20standpoint%2C%20maybe%20you%20could%20enter%20the%20data%20as%20a%20decimal%20(20.10%20for%2020%20min%2010%20sec)%20and%20modify%20your%20summary%20formulas%20(depending%20on%20how%20you%20are%20using%20the%20data%20downstream%20and%20the%20desired%20format%20of%20the%20summary%20data)%3F%20I'm%20partial%20to%20this%20when%20keying%20a%20lot%20of%20time%20data%20as%20I%20can%20key%20it%20faster%20as%20a%20decimal%20than%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20a%20workbook%20as%20an%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1515284%22%20slang%3D%22de-DE%22%3ERe%3A%20Formatting%20Time%20(MM%3ASS)%20without%20using%20hours%20and%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1515284%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EI'm%20not%20sure%20what%20you%20want%20but%20I%20think%20this%20is%20in%20the%20right%20direction%20...%20see%20sheet%20%3C%2FSPAN%3E%3C%2FSPAN%3E%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1515681%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20Time%20(MM%3ASS)%20without%20using%20hours%20and%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1515681%22%20slang%3D%22en-US%22%3EI'm%20not%20looking%20for%20anything.%20The%20data%20in%20Column%20A%20is%20mm.ss%20(it's%20keyed%20in%20as%20a%20decimal%2C%20but%20A1%20is%20supposed%20to%20represent%2081%20minutes%2C%2020%20seconds).%20This%20is%20easy%20for%20keying%20purposes%2C%20but%20obviously%20any%20summary%20total%20would%20be%20wrong%20as%200.20%20is%2C%20mathematically%2C%20not%2020%20seconds).%3CBR%20%2F%3E%3CBR%20%2F%3EColumn%20D%20is%20three%20different%20versions%20of%20how%20the%20decimal%20data%20could%20be%20converted%20to%20time%20and%20totaled.%20Column%20A%20total%20is%20222%20minutes%2C%2020%20seconds%2C%20which%20can%20expressed%20as%20a%20decimal%20(222.33%20minutes)%2C%20or%20you%20could%20have%20it%20as%20222.20%20(mm.ss)%2C%20or%20the%20traditional%20time%20format%20222%3A20.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20I'm%20keying%20a%20lot%20of%20time%20data%20in%20the%20format%20mm%3Ass%20from%20hardcopy%20time%20logs%2C%20then%20I%20wouldn't%20want%20to%20key%20it%20in%20the%20traditional%20h%3Amm%3Ass%20format.%20I%20would%20enter%20it%20as%20a%20decimal%20as%20I'm%20faster%20at%20data%20input%20with%20a%20ten%20key%20using%20a%20decimal.%20But%2C%200.20%20in%20decimal%20has%20to%20be%20converted%20if%20I%20intend%20it%20to%20represent%2020%20seconds%20when%20I%20total%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517309%22%20slang%3D%22de-DE%22%3ERe%3A%20Formatting%20Time%20(MM%3ASS)%20without%20using%20hours%20and%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517309%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Ei%20don't%20understand%20what%20is%20wanted.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22%22%3E81.20%20decimal%20is%2081min%20%26amp%3B%2012%20sec.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3Ethis%20can%20be%20calculated%20with%20the%20formula%20and%20the%20correct%20line%20formatting%20(H1%20%3D%20formula%20for%20line%20formatting%20-%20right%20mouse%20click).%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22%22%3EYou%20can%20use%20the%20formula%20in%20E1%20to%20determine%20whether%20it%20should%20be%20added%20up%20or%20individually%20appear.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%3Ein%20H1%20is%20the%20line%20formatting%20for%20E1%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EIn%20E1%20the%20formula%20is%20formatting%20for%20the%20calculation%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EWhere%20is%20my%3CSPAN%3Emistake%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I work in manufacturing, and am trying to add to excel a minutes and second column for showing how long it takes to make each part for a full item. I am needing to be able to add up all of the minutes and seconds as well without them being in relation to a date or the 24 or 12 hour clock.. if that makes sense. 

 

I have tried the mm:ss and mm:ss.0 but nothing works. I am not wanting to have to input hours as I don't need them. 

 

Can anyone help?

5 Replies
Highlighted
Select lines where you write the time, right mouse button, select format lines, click on numbers, click on the date and there you can choose any time format you want. Then you can enter your time in the selected lines and add up as you like.

With User define there are also some ways to play with time.
User defined: h: mm; @

If you want something more specific you have to upload a file so that we can understand your plan.

*I know that I know nothing (Sokrates)
Highlighted

@cpowelske 

 

As far as formatting, I think [m]:ss is what you're looking for. But, it appears excel still requires you to key the hour when you enter the data. So, you'd have to key a 0 placeholder (0:20:36 for 20 minutes, 36 seconds).

 

If the extra keying of the leading 0 is an issue from a data entry standpoint, maybe you could enter the data as a decimal (20.10 for 20 min 10 sec) and modify your summary formulas (depending on how you are using the data downstream and the desired format of the summary data)? I'm partial to this when keying a lot of time data as I can key it faster as a decimal than time.

 

I attached a workbook as an example.

Highlighted

@JMB17 

Hi,

I'm not sure what you want but I think this is in the right direction ... see sheet.

 

Nikolino

Highlighted
I'm not looking for anything. The data in Column A is mm.ss (it's keyed in as a decimal, but A1 is supposed to represent 81 minutes, 20 seconds). This is easy for keying purposes, but obviously any summary total would be wrong as 0.20 is, mathematically, not 20 seconds).

Column D is three different versions of how the decimal data could be converted to time and totaled. Column A total is 222 minutes, 20 seconds, which can expressed as a decimal (222.33 minutes), or you could have it as 222.20 (mm.ss), or the traditional time format 222:20.

If I'm keying a lot of time data in the format mm:ss from hardcopy time logs, then I wouldn't want to key it in the traditional h:mm:ss format. I would enter it as a decimal as I'm faster at data input with a ten key using a decimal. But, 0.20 in decimal has to be converted if I intend it to represent 20 seconds when I total it.
Highlighted

@JMB17

 

i don't understand what is wanted.

81.20 decimal is 81min & 12 sec. this can be calculated with the formula and the correct line formatting (H1 = formula for line formatting - right mouse click).

You can use the formula in E1 to determine whether it should be added up or appear individually.

in H1 is the line formatting for E1

In E1 the formula is formatting for the calculation


Where is my mistake?