Difference in longitude between two hemispheres

[l1011driver]

One formula in my spreadsheet needs to know how many degrees of longitude exist between two geographical points. I'm really scratching my head on how to calculate this difference when the points of longitude are in different hemispheres.

For example, if one point is at 140 degrees east longitude (denoted as 140) and the other point is at 140 degrees west longitude (denoted as -140), that's a difference of 80 degrees of longitude. Can anyone help me out with a formula that will make this calculation? I would like to find a formula that would correctly calculate the difference in longitude between two points in the same hemisphere as well.

Thank you to anyone who has any suggestions.

Sincerely,

L1011driver

 

[Georgiboy]

Two methods below, there may be other ways to do it:

Book1
	A	B	C
1	-140	140
2
3			-80
4			-80
Sheet1

Cell Formulas
Range	Formula
C3	C3	=IF(ABS(B1-A1)<=180,B1-A1,IF(B1>A1,B1-A1-360,B1-A1+360))
C4	C4	=MOD(B1-A1+540,360)-180

 

[l1011driver]

Hi Georgiboy. Both of these formulas worked perfectly. I kept fiddling with the MOD function but I just couldn't figure out how to get it to work. I see how you did it and it makes perfect sense.

I gotta ask: are you from Georgia? I lived in Savannah for several years.

Thank you so much for helping. Take care and stay safe.

Sincerely,

L1011driver

 

[Georgiboy]

Glad you got it sorted, I am not from Georgia - I am from the Garden of England (Kent, UK) but my name is George

 

[l1011driver]

Glad you got it sorted, I am not from Georgia - I am from the Garden of England (Kent, UK) but my name is George

Hi Georgiboy,

You were so great before, perhaps I can ask you another one?

I have a spreadsheet that has time in 1 minute increments. In an adjacent column I have a date corresponding to each 1-minute increment of time.

What I would like to do is have the date cell advance to the next day when the corresponding time cell reaches midnight (00:00). Subsequent date cells would reflect the "new" date until again reaching midnight on that day.

If you have any suggestions or help I'd be grateful. It seems my laptop doesn't like XL2BB so I'll attach a screen shot of what I'm talking about. I use a formula for column C, but column B is just the date copied to each subsequent cell. The formula bar shows the formula I use for the 1-minute increments of time.

Thanks for any suggestions you may have.

Sincerely,

Mark Barnard

 

[Georgiboy]

Hi Mark,

As this is not related to the original request - you should start a new thread (I will be happy to look at it though)

It will be worth updating your profile to show what version of Excel you are using as this will change the solutions offered. If it is to be used on an older version than what your profile displays then it would be worth mentioning that.

George

 

[l1011driver]

Hi Mark,

As this is not related to the original request - you should start a new thread (I will be happy to look at it though)

It will be worth updating your profile to show what version of Excel you are using as this will change the solutions offered. If it is to be used on an older version than what your profile displays then it would be worth mentioning that.

George

Thanks, George. I will put it in a new thread. I just kind of enjoyed dealing with you. You're very patient with an old guy.

I'll look at how to update the profile, too. I use Excel 365.

Thanks and take care.

Sincerely,

Mark