Date subtraction with result in seconds and modular division problem

Ed Harris

New Member
Joined
Dec 9, 2017
Messages
31
Office Version
  1. 2010
Platform
  1. Windows
I am trying to subtract a load of dates from an earlier date to end up with seconds which I can then do modular division on with the number of seconds in a sidereal day. This will give me time separation that I can sort to find similar location of points in the celestial sphere. I simply subtracted one of the dates D3 from the earlier date D2 then tried to do the mod division on it and got the same result. I think Iam confused what format these should be in. Or that result is decimal time? Help much appreciated.

Experiment index.xlsx
DEF
201/01/2017 00:00
320/08/2019 02:29961.10961.10
Sheet2
Cell Formulas
RangeFormula
D3D3=B3+C3
E3E3=D3-D2
F3F3=MOD(E3,86164.0935534009)
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows
The result in E3 is showing days, not seconds.

If you want the no of seconds between the 2 dates multiple the difference in days by 24 (hrs) by 60 (mins) by 60 (secs).
Excel Formula:
=(D3-D2)*24*60*60
 
Solution

Ed Harris

New Member
Joined
Dec 9, 2017
Messages
31
Office Version
  1. 2010
Platform
  1. Windows
Many thanks, that has solved the problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,464
Messages
5,636,432
Members
416,918
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top