Free Microsoft Excel 2013 Quick Reference

converting from digital degrees to degrees minutes seconds

I need to convert latitudes and longitudes from digital degrees ( as 23.36
º) to degrees minutes seconds (as 23º 39' 38'')
I found a way to do it for latitudes ( dividing by 24 and choosing hh:mm:ss
format) but doesnt' work for longitudes ( I tried dividing by 48 instead
considering the difference between 90 degres for latitud and 180 degrees for
longitud, but doesn't work, either)

The second problem I have is when I import GPS files with coordinates from
CSV files, I always get digital degrees, yet those files have both options
(digital degrees and degrees minutes seconds)

Post your answer or comment

comments powered by Disqus
is there any way in excel to convert from Hijri Date to Gregorian. in column A I have multiple Cells with Hijri Date, in column B a formula to convert the date in column A which is Hijri to be Gregorian
thank you in advance


Currenly i have a set of coordinates with a set of decimal degrees which i have been trying to convert to degrees, minutes and seconds. however, I have not been able to find a correct way to convert the negetive coordinates to the proper coordinate.

using H2 as the referance cell,

=INT(H2) - decimal
=INT(MOD(H2,1)*60) - minutes
=MOD(H2*3600,60) - seconds

the problem is that when the decimal degree i have is -4.10885429, i end up with

-5° 32' 7.00368"

I have been doing alot of searching around dealing with the conversions which is where i found the formulas for the initall degrees, minues, seconds but cant seem to find anything showing how to deal with those pesky negitive numbers. Thank you

Hi guys,

how do you convert a decimal degrees to degrees minutes and seconds?

something like 1o 20' 30" ?

I'm able to convert radiants to degrees but don't know how to
show it with the above format

thanks for yr help in advance

Does anyone know how to convert lat/lon coordinates from degrees to degrees
minutes seconds in the format of (DDD.DDDD to DDD MM SS.SS)?

Hello all

Part One
I am trying to find a way to create a macro that will convert the numbers in column B, on the Master worksheet, of the attached file from a number to its time. For example I would like the number 1224 in B1 to be 12:24.

Part Two
Once the above conversion is done I then need to get the difference between the times shown going down the column. What I mean is that B1 is the start of a test and B2 is the start of a new test, and B3 is a start of the third consecutive test and so on. I will be sorting these by test type and will need to determine the test run length based on the start of a test and the start of the test after it. As you will note some of the test actually have a TimeTo Run feature and these tests are fine. I will be adding new tests that do not have this TTR variable and thus need to extrapolate the Test Run from start times of the test in question and the test that follows it.

For example if the test 1 began at 12:25 and the test 2 began at 17:05 then I want to know that the test run took 4 hours and 40 minutes.


Hi all,

Has anyone had any issues with graphs disappearing when they convert a spreadsheet from Excel 2003 to 2007. I'm assuming I'm using a feature that is not available in 2007, but I'm not sure which one.

I have a bunch of dymanic graph spreadsheets. Each contains anywhere from ten to 45 graphs of statistical information. The graphs display the data based on the date I enter/select in cells on the parm worksheet. Most of the graphs have disappeared and all I see is a blue square, no cell lines, no graph. A few of the graphs are fine, about 7 out of the 45 graphs in this particular spreadsheet.

What I did was open the 2003 (.xls) spreadsheet in Excell 2007. Excel 2007 did the conversion. Then save it as a .xlsm spreadsheet. Then I opened the xlsm spreadsheet and the graphs were gone. The graphs were there when I opened the .xls in 2007.

I've attached two spreadsheets. One is the original and the second is the results of saving it as .xlsm. I had to delete most of the graphs and a good portion of the data to get the spreadsheet to 1 meg upload size. The .xls is a 8 meg spreadsheet and the .xlsm is a 2.9 Meg.

I have been given an Excel spreadsheet with numbers in 8-digit text format.
For example, 20020812 would be August 12, 2002. How do I convert these to
some sort of date field that will allow me to perform calculations (count
days from one date to the next)?

I'm looking for a formula that will convert a time in minutes and seconds to
total seconds. EX 3:46 which is 3 minutes and 46 seconds and is 226 seconds

I am trying to convert a column of numbers into hours:minutes:seconds.

For example, I have following numbers (original data followed by desired output). I tried using the custom number format but could not get the desired result. Any assistance appreciated.

9 - convert to :09
53 - convert to :53
68 - convert to 1:08
109 - convert to 1:49
121 - convert to 2:01
262 - convert to 4:22

I need to convert long time format to all minutes

So I need to convert this format:
1 days 7 hrs 28 min 21 sec
to this format:
and this format:
1 hrs 10 min 3 sec
to this format:
I tried performing a Find and Replace but there are some cells that only have seconds and they kept being reported as 14:00 instead of 14.

Attached is a sample of what the before picture looks like.
Does anyone have any suggestions on how I might be able to do this?


Is there any way that I can have a column of cells change the entered 4 digit to a time format? E.g. when somebody enters 1212 in a cell it will automatically convert the entered numbers to 12:12


I have a hundred or so Excel workbooks linked to a hundred or so other workbooks created by other individuals.
I am finally trying to convert the xls formats to xlsx (I currently have both 2003 and 2010 on my machine).

Previous versions of Excel did not require the linked workbooks to be open in order to update. Currently when I open a linked workbook there are zeroes or blanks where I know there should be numbers. When I edit a cell I get the expected results. Are my options either to open all the linked workbooks or manually edit (F2) each formula which contains a link? Will there be issues with the users with older versions?

Not everyone in the company has upgraded to 2010 - we have versions from 2000 to 2010 in use - fortunately we will all be on 2010 by next Spring but in the meantime I am expecting growing pains with everyone using a different version.

I downloaded the suggested Excel 2010 overview guide and have the Walkenbach Office 2010 Bible but have not found where they really address this issue. Thank you in advance for your response. Any comments or suggestions to make this conversion easier are welcome.

May I know how to convert from PPT format to Excel format using VBA???

When I convert some worksheets from Lotus 123 to Excel, the gridlines do not
display, even though the gridline box is checked. This does not occur with
every worksheet, just a few.


I need to write a macro that will change the following: -

09/27/2004 to 27/09/2004

In other words from US format to UK format.

I have a number of columns that need to be converted at the same time.

Any help would be appreciated



I am creating a dialy time record for reporting to an online time card
system. I need to convert from excel's time format to decimal format and
figure the difference from start time and finish time in 1/10th hour
increments and have it displayed it as such.

I am having trouble converting from comma delimited to to tab delimited.
When I convert from a comma delimited file to a Excel file the rows and
columns are fine. When I try to save as a tab delimited file it throws my
alignments and feilds off. I have checked in notepad that Word Wrap is off.
The person I am working with can convert this file fine on his workstation,
but I have tried 10 other workstations and they do not convert correctly.


I am new to excel and I need help. I need to convert hours 01:10 to minutes 70. For one column A. What is the formula and how to I format it for the whole column not just one cell?

I have to debug an application that was converted from Excel 97 directly to
Excel 2003. Everything works fine except for the pivot tables. They are not
valid anymore and I can't replace the fields because I get an error message
saying that the pivot table is not valid.
The Pivot tables are not created with code but a lot of code exists that
access them to validate the data or to calculate some other figures.
Will I have to delete the pivot tables and rebuild them all together or is
there a way to fix the problem by correcting the references to the original
Thank you in advance.
Jac Tremblay

I have an EXCEL 2003 spreadsheet containing a column with zip codes in this

How can I convert these zip codes to a column that just has the first five
digits, that is:


I will appreciate advice.

I guess my subject line says it all. I have tried cartisian co-ordinates to
convert from XY to Polar Graph, but I need to plot it on a radar chart. Can
anyone help me please?

Dave Whitehead


I need help with a formula. As of now, I have a column with standard time. For instance, the column looks like:

7:58 AM (CDT)
4:24 PM (CDT)

I am looking to remove "(CDT)" from the text. I know the formula for that would be something like =LEFT(Column, # of characters). However, I would also like to convert the standard time to military time, and then I want to convert the times into a decimal. Is there a way I can do all of this in one formula? Thank you for your help.

Background story:

Hi. I wanted to make an own thread for this because its not as simple as the title says.

I have sort of a "diary" that has 2 worksheets. On the first worksheet you enter the values of happenings to certain cells. Second worksheet is designed to store these values for each day. The values that needed to be copied are date, time, event and note. I already have a macro for date in the workbook that can be used for copying. Time, event and note is written by the user. User selects a certain event by clicking a box.

I would like to have a macro that works when I press a button. The macro would copy the date, time, event and note to the second worksheet. Note that the event is selected with "click the box" way.

At the moment there are 2 events the user can select. "Guest" and "Alarm". Now, if the user selects only "Guest" how do I make excel know that I only want that box to be copied and not the other? And how does excel know what the selected boxes value is? The value is written next to the box, but does excel know to copy that only when the box is marked?

The Question:

So, the cells I'd want excel to copy are: EFG-567 + E15 OR E16 (event) + H14-15 + I14-15 + JKLMN-14-21.

First one is for date, second is for event, third one is for time and the last is for note. All are just simple "copy", except the event, because I dont know how it works with the boxes.

Now, I would like excel to copy the values from those cells to the second worksheet's cells: date to cell's AB3, time to cell's CD3, event to cell's EF3 and note to cell's GHI3.

That would be the data for the first day.. next day would be the same except it would be row 4. Now, each time a copy is made, it should NEVER be copied to a cell that already has data.

How do I make all this happen? Thanks in advance.

I downloaded a converter from the web to convert a Quattro Pro 8.0
spreadsheat to Excel 2002. Now there are certain cells that I cannot fromat.
I use the format cells command but nothing happens. The cells were not locked
in Quattro Pro.

No luck finding an answer? You could always try Google.