asked on

Excel default cell value display

Without touching the default cell format for cell range A7-A20, I want to copy over the values in cell range A7-A20 (i.e. 73:24:58) into cell range G7-G20 with the custom Lat/Long cell format.

The problem is that if I enter 73:24:58 into Excel cell A7 it automatically converts its value to 1/3/1900 0:24 in the formula bar even if the actual cell still shows 73:24:58. If I click on cell A7 it changes it to 1/3/1900 0:24. I know its because the cell format is defaulted to the date but I cannot change that since other formulas are dependent on that setup.

So, I want to add another formula to cell range G7-G20 that converts values like 73:24:58 into lat/long but Excel is reading 73:24:58 as 1/3/1900 0:24. How can I make it read the 73:24:58 in cell A7 and display as 72° 24' 58'' into cell G7? I already have the cell formatted for lat long in cell G7. It's just that Excel is displaying the value as ° 00' 03''.
example.xlsx

byundt

There is no lat long format in Excel. But you can fake it using a Custom time format:
[h]° mm' ss''

See how I modified your cell G7
Lat-long-format-Q28077901.xlsx

ASKER CERTIFIED SOLUTION

byundt

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

jj1103

ASKER

Hi byundt, Thank you for your reply. I already had the cells set up for the lat/lon in the G7 range that worked great. The G7 range cells were already set with a custom format of ###° 00' 00''. But your format of [h]° mm' ss worked better!

ThankYOU :)