SOLVED
Home

Time data issues

%3CLINGO-SUB%20id%3D%22lingo-sub-307780%22%20slang%3D%22en-US%22%3ETime%20data%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307780%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3EAs%20I%20mentioned%20in%20my%20first%20post%2C%20I%20am%20a%20complete%20novice%20with%20Excel%2C%20so%20I%20will%20probably%20be%20here%20often.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20a%20terrible%20time%20working%20with%20elapsed%20time%20data.%20I%E2%80%99m%20trying%20to%20enter%20the%20time%20as%20minutes%20and%20seconds%20and%20get%20an%20average%20of%20the%20data.%20On%20the%20included%20worksheet%2C%20rows%207%20and%2011%20are%20the%20time%20rows.%20Both%20are%20now%20formatted%20h%3Amm.%20(I%20couldn%E2%80%99t%20make%20mm%3Ass%20work%20either.)%20In%20row%207%2C%20the%20data%20has%20a%20time%20value%20from%20B7%3AJ7%3B%20an%20%E2%80%98as%20entered%E2%80%99%20value%20(like%20'Text')%20from%20K7%3AR7%3B%20and%20from%20S7%3AAM7%2C%20the%20value%20reverts%20back%20to%20time.%3CBR%20%2F%3EIn%20row%2011%2C%20the%20data%20has%20an%20%E2%80%98as%20entered%E2%80%99%20value%20from%20B11%3AAI11%20then%20changes%20to%20a%20time%20value.%20Also%2C%20the%20data%20entered%20in%20AL11%20just%20isn%E2%80%99t%20right.%20When%20I%20enter%2025%3A58%2C%20it%20shows%20in%20the%20cell%20as%201%3A58%20and%20a%20time%20value%20in%20the%20formula%20bar.%20As%20near%20as%20I%20can%20tell%2C%20none%20of%20the%20other%20cells%20behave%20this%20way.%20As%20a%20result%20of%20this%2C%20the%20average%20function%20is%20going%20nuts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99ve%20tried%20converting%20the%20entered%20time%20values%20to%20decimal%3B%20getting%20the%20average%3B%20and%20putting%20the%20result%20back%20into%20mm%3Ass%2C%20but%20either%20that%20won%E2%80%99t%20work%2C%20or%20I%E2%80%99m%20doing%20something%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99m%20using%20Excel%20Office%20365.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERick%20Lizotte%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-307780%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308209%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20data%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308209%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Mourad%2C%20that%20did%20the%20trick.%26nbsp%3B%20I%20had%20no%20idea%20the%20formatting%20didn't%20apply%20immediately.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou've%20been%20a%20big%20help.%26nbsp%3B%20Thanks%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERick%20Lizotte%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307913%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20data%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307913%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Rick%2C%3C%2FP%3E%0A%3CP%3Eit%20looks%20like%20that%20some%20entries%20were%20e.g.%20entered%20as%20text%20and%26nbsp%3Bpreserves%20that%20even%20after%20having%26nbsp%3Bchanged%20the%20format%20to%20e.g.%20%5Bh%5D%3Amm.%20Please%20e.g.%20double%20click%20the%20value%20and%20press%20Enter.%20Will%20then%20change%20the%20value%20to%20a%20time%20value%20(appears%20then%20as%20e.g.%2015%3A53%3A00%20in%20the%20formula%20bar).%3CBR%20%2F%3EBest%2C%3C%2FP%3E%0A%3CP%3EMourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307837%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20data%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307837%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22gmail_default%22%3EThanks%20Mourad%2C%26nbsp%3B%20that%20worked%20for%20that%20specific%20cell%2C%20but%20why%20do%20the%20cell%20entries%20in%20both%20rows%207%20and%2011%20show%20a%20combination%20of%20text%20entries%26nbsp%3B%20and%20time%20entries%20when%20both%20rows%20are%20formatted%20as%20time%3F%26nbsp%3B%20I%20don't%20understand%20that%20at%20all.%26nbsp%3B%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22gmail_default%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22gmail_default%22%3EThanks%20again%20for%20your%20help%2C%3C%2FDIV%3E%3CDIV%20class%3D%22gmail_default%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22gmail_default%22%3ERick%20Lizotte%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307795%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20data%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307795%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Rick%2C%3C%2FP%3E%0A%3CP%3Ewith%20your%20choosen%20custom%20format%20%3CEM%3Ehh%3Amm%3C%2FEM%3E%2C%20the%20display%20does%20not%20take%20into%20account%20the%20number%20of%20elapsed%20days.%26nbsp%3BTherefore%20it%20shows%20you%2001%3A58%20%3D%2025%3A58-24%3A00%20in%20the%20cell.%20The%20formula%20bar%20correctly%20shows%201%20day%20(01.01.1900)%20%2B%20the%20time%20(01%3A58).%20You%20can%20enclose%20the%20hour%20code%20with%20squared%20brackets%20in%20your%20custom%20format%20for%20including%20the%20elapsed%20days%20into%20the%20display%2C%20e.g.%20%3CEM%3E%5Bh%5D%3Amm%3C%2FEM%3E.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F64463i9F617EE476E2DDDE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20have%20a%20look%20i%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fformat-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-b4d30c528309%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3En%20this%20article%20for%20more%20possiblities%3C%2FA%3E%20regarding%20date%20and%20time%20formats%20in%20Excel.%3C%2FP%3E%0A%3CP%3EBest%2C%3C%2FP%3E%0A%3CP%3EMourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
Rick.Lizotte
Occasional Contributor

Hello all,

As I mentioned in my first post, I am a complete novice with Excel, so I will probably be here often.

 

I am having a terrible time working with elapsed time data. I’m trying to enter the time as minutes and seconds and get an average of the data. On the included worksheet, rows 7 and 11 are the time rows. Both are now formatted h:mm. (I couldn’t make mm:ss work either.) In row 7, the data has a time value from B7:J7; an ‘as entered’ value (like 'Text') from K7:R7; and from S7:AM7, the value reverts back to time.
In row 11, the data has an ‘as entered’ value from B11:AI11 then changes to a time value. Also, the data entered in AL11 just isn’t right. When I enter 25:58, it shows in the cell as 1:58 and a time value in the formula bar. As near as I can tell, none of the other cells behave this way. As a result of this, the average function is going nuts.

 

I’ve tried converting the entered time values to decimal; getting the average; and putting the result back into mm:ss, but either that won’t work, or I’m doing something wrong.

 

I’m using Excel Office 365.

 

Any help would be greatly appreciated,

 

Rick Lizotte

4 Replies

Hi Rick,

with your choosen custom format hh:mm, the display does not take into account the number of elapsed days. Therefore it shows you 01:58 = 25:58-24:00 in the cell. The formula bar correctly shows 1 day (01.01.1900) + the time (01:58). You can enclose the hour code with squared brackets in your custom format for including the elapsed days into the display, e.g. [h]:mm.

image.png

Please have a look in this article for more possiblities regarding date and time formats in Excel.

Best,

Mourad

Thanks Mourad,  that worked for that specific cell, but why do the cell entries in both rows 7 and 11 show a combination of text entries  and time entries when both rows are formatted as time?  I don't understand that at all.  
 
Thanks again for your help,
 
Rick Lizotte
Solution

Hi Rick,

it looks like that some entries were e.g. entered as text and preserves that even after having changed the format to e.g. [h]:mm. Please e.g. double click the value and press Enter. Will then change the value to a time value (appears then as e.g. 15:53:00 in the formula bar).
Best,

Mourad

Thanks Mourad, that did the trick.  I had no idea the formatting didn't apply immediately.

 

You've been a big help.  Thanks again.

 

Rick Lizotte

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies