Format of data in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1509048%22%20slang%3D%22en-US%22%3EFormat%20of%20data%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1509048%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20format%20in%20my%20column%20is%20%22Custom%22%26nbsp%3B%20%5Bh%5D%3Amm%3Ass.%20The%20entry%20is%2032%3A22%3A00%26nbsp%3B%20In%20the%20dialog%20box%2C%20it%20shows%20as%20a%20DATE%20and%20time%2C%201%2F1%2F1900%208%3A22%3A00%20AM!!%26nbsp%3B%20How%20logical%20is%20THAT%3F%26nbsp%3B%20When%20I%20want%20to%20use%20the%20data%20on%20that%20cell%20to%20determine%20a%20miles%20per%20hour%20rate.%20The%20completely%20insane%20return%20is%2066%3A59%3A02.%26nbsp%3B%20I%20rowed%203%2C76%20miles%20in%2032%3A22%3A00%20so%20the%20answer%20should%20be%20in%20the%20range%20of%206%20-%207%20mph.%26nbsp%3B%20I%20do%20not%20get%20anything%20close%20to%20that%20regardless%20of%26nbsp%3B%20what%20format%20I%20apply%20to%20the%20cell%2C%26nbsp%3B%20If%20I%20format%20a%20different%20cell%20as%20Custom%2F%20h%3Amm%2C%20and%20enter%20%3D32%3A22%3A00%2F60%20to%20get%20the%20time%20as%20fraction%20of%20an%20hour%2C%20the%20answer%20is%200%3A32.%26nbsp%3B%20Again%2C%20formatting%20that%20cell%20in%20which%20ever%20way%2C%20yields%20a%20useless%20and%20inaccurate%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20worksheet%20displays%20much%20data%20of%20a%20rowing%20machine%20workout%2C%20but%20is%20more%20of%20a%20workout%20than%20the%20rowing%20itself%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1509048%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E(Non)sense%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1509069%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20of%20data%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1509069%22%20slang%3D%22en-US%22%3ETo%20add%20conditional%20formatting%20to%20your%20worksheet%2C%20select%20the%20cells%20you%20want%20to%20showcase%20and%20click%20Conditional%20Formatting%20in%20the%20Styles%20group%20of%20the%20Home%20tab.%20Select%20the%20style%20of%20formatting%20you%20want%20to%20apply%2C%20and%20Live%20Preview%20will%20show%20how%20the%20formatting%20will%20look%20on%20your%20data.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

The format in my column is "Custom"  [h]:mm:ss. The entry is 32:22:00  In the dialog box, it shows as a DATE and time, 1/1/1900 8:22:00 AM!!  How logical is THAT?  When I want to use the data on that cell to determine a miles per hour rate. The completely insane return is 66:59:02.  I rowed 3,76 miles in 32:22:00 so the answer should be in the range of 6 - 7 mph.  I do not get anything close to that regardless of  what format I apply to the cell,  If I format a different cell as Custom/ h:mm, and enter =32:22:00/60 to get the time as fraction of an hour, the answer is 0:32.  Again, formatting that cell in which ever way, yields a useless and inaccurate answer.

 

This worksheet displays much data of a rowing machine workout, but is more of a workout than the rowing itself

2 Replies
Highlighted
To add conditional formatting to your worksheet, select the cells you want to showcase and click Conditional Formatting in the Styles group of the Home tab. Select the style of formatting you want to apply, and Live Preview will show how the formatting will look on your data.
Highlighted

@Lewis-H 

 

Thanks! I will try this over the weekend.