Visit our Popular Forums

transmitterdan · 06-01-2019, 13:45

Quote:

Originally Posted by sy_gilana

Here is another sexy formula, but I don't know if it will work in Excel, which makes mistakes (like 360 days in a year) Don't use excel to work out your vacation time, you will lose 5.25 days.

That’s rubbish. Subtract 1/1/2018 from 1/1/2019 in Excel and you get exactly 365.

sy_gilana · 06-01-2019, 15:07

Quote:

Originally Posted by transmitterdan

That’s rubbish. Subtract 1/1/2018 from 1/1/2019 in Excel and you get exactly 365.

So just a simple subtract, instead of using the formula "Days360" ?
like =(days360(cell1,cell2)) I wonder why there is no "days365"
Anyhoo this is off topic so less said the better. You might gather that I have no microsoft or apple stuff anywhere near me...

I realise my previous post did not include the spreadsheet mentioned, and then I saw that the file type was not allowed, so here it is, please remove the ".doc" and it is in open spreadsheet format.

transmitterdan · 06-01-2019, 16:26

The doc say:

The DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.

So nothing wrong with excel at all. We could all do better if we stick to describing things we actually know about.

sy_gilana · 06-01-2019, 17:13

Quote:

Originally Posted by transmitterdan

The doc say:

The DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.

So nothing wrong with excel at all. We could all do better if we stick to describing things we actually know about.

Ok so I know nothing about Excel, I'm happy to say, however, the question I was answering dealt with changing decimal positions as used in ECDIS systems converted to Human Readable format. My spreadsheet refers. Hopefully it helped.

(incidentally on Openoffice, the function "DAYS" uses 365, I could not find an equivalent in XL, never thought about just subtracting the dates)

sloppy · 08-01-2019, 00:19

Quote:

Originally Posted by sy_gilana

Theres Nohal's answer, (and they are always good) but you can also use non microsoft polluted spreadsheets, like the attached one...where I change from machine-readable latlongs to human readable, and include the N to S and E to W decisions.

Here is another sexy formula, but I don't know if it will work in Excel, which makes mistakes (like 360 days in a year) Don't use excel to work out your vacation time, you will lose 5.25 days.

=ACOS( SIN((B3)*PI()/180)*SIN((B4)*PI()/180) + COS((B3)*PI()/180)*COS((B4)*PI()/180)*COS((C4)*PI()/180-(C3)*PI()/180) ) * 3440

Where B3 and B4 are two waypoints. (3440 is the radius of the earth measured in shot shackles)

Holy smoke man this is way over my head, to start with where do I jot down these formulas? When I have more time will look into it to see if I am lucky. Thanks again.

sloppy · 08-01-2019, 00:35

Quote:

Originally Posted by sy_gilana

Theres Nohal's answer, (and they are always good) but you can also use non microsoft polluted spreadsheets, like the attached one...where I change from machine-readable latlongs to human readable, and include the N to S and E to W decisions.

Here is another sexy formula, but I don't know if it will work in Excel, which makes mistakes (like 360 days in a year) Don't use excel to work out your vacation time, you will lose 5.25 days.

=ACOS( SIN((B3)*PI()/180)*SIN((B4)*PI()/180) + COS((B3)*PI()/180)*COS((B4)*PI()/180)*COS((C4)*PI()/180-(C3)*PI()/180) ) * 3440

Where B3 and B4 are two waypoints. (3440 is the radius of the earth measured in shot shackles)

If not microsoft, what program do you use?

nohal · 08-01-2019, 04:01

Quote:

Originally Posted by sloppy

If not microsoft, what program do you use?

It works exactly the same in the free LibreOffice Calc. Use whatever you have, of course.

sy_gilana · 08-01-2019, 11:05

Quote:

Originally Posted by sloppy

Holy smoke man this is way over my head, to start with where do I jot down these formulas? When I have more time will look into it to see if I am lucky. Thanks again.

Hi, I'm sorry, I did not mean to confuse you.

The post I was replying to asked about changing

40.71333333 and 14.466666667 to

40, 42.80 N and 14,28.00 E

The spreadsheet (Open Office) that I use to do this is attached to post number 77 above. I use this often to swap tracks and routes created in an ECDIS like Transas, to OpenCPN to be able to extract information, such as how often a vessel traversed the territorial waters of a particular jurisdiction, or where it was with regard to currents when discharges were made.

If you open the spreadsheet and paste your decimal lat and long into the first two columns, you will get the more understandable answer in the calculated columns, its not complicated, first the ABS value is taken out, then the mantissa is multiplied by 60. These columns are then exported as a CSV and imported into OpenCPN where I can start doing my analysis of the vessel tracks.

There are some online calculators that work rather well, gpsbabel.com.

The second rather complicated formula is only to calculate the distance between waypoints, its just spherical geometry, not needed in your case, I copied it into the post in case another member might be interested in it. (for example another nerd like me)

sloppy · 10-01-2019, 00:13

Opened it up with Word andI think the archive is damaged, the second column does not show,also have trouble jotting down waypoint decimals. Trying to add capture so you can see how it opened up but can not do that either

sloppy · 10-01-2019, 00:18

Added the capture but I do not think it posts. Oh ok just realized it says...Add Video, maybe thats why

sy_gilana · 10-01-2019, 10:01

Ok, it should not be opened with WORD.
I borrowed a computer and made an EXCEL version attached.

Columns B and C are where you should paste your numbers.
Click, but don't change the contents of other cells, but there you can see the working out, and the results should be in the corresponding cells.

sloppy · 10-01-2019, 10:36

Gosh man thanks a whole bunch, will try it with both open office and excell....cool!

sloppy · 10-01-2019, 10:44

Quote:

Originally Posted by sy_gilana

Ok, it should not be opened with WORD.
I borrowed a computer and made an EXCEL version attached.

Columns B and C are where you should paste your numbers.
Click, but don't change the contents of other cells, but there you can see the working out, and the results should be in the corresponding cells.

Ok, pasted lat,long. in B and C but surely I have to click on something because nothing happened