How to add seconds to a date and time?

[Ed Harris]

I am trying to make a time correction due to computer clock running slow. For some reason the time correction in column AE of the table below won't update. It doesnt make any difference if I use ordinary date format or excel time code format as source. I have tried using the value in column AD as numeric value (ie not a product of a formula in the cell) as I have found that sometimes works but this time it failed (rows 4 to 6). As far as I know deviding by 86400 converts the seconds into excel second format so if you dont do that as in the last three rows it changes the date. Any help much appreciated.

Beam1 2021.xlsx
	AA	AB	AC	AD	AE
1	Date	Date clock corrected	excel format of date	time correction seconds	Corrected date
2
3	05/01/2021 08:33	04/11/2020 00:00	44139.00	3.79	05/01/2021 08:33
4	05/01/2021 13:48			3.81	05/01/2021 13:48
5	07/01/2021 14:10			3.93	07/01/2021 14:10
6	06/02/2021 09:24			5.74	06/02/2021 09:24
7	16/02/2021 00:54			6.33	16/02/2021 00:54
8	17/02/2021 06:06			6.40	17/02/2021 06:06
9	21/02/2021 09:16			6.65	28/02/2021 00:55
10	25/02/2021 08:58			6.89	04/03/2021 06:26
11	26/02/2021 08:58			6.96	05/03/2021 07:54
Sheet1

Cell Formulas
Range	Formula
AC3	AC3	=AB3
AD9:AD11,AD3:AD5	AD3	=(AA3-$AB$3)*9/148
AE3:AE8	AE3	=AA3+(AD3/86400)
AE9:AE11	AE9	=AA9+AD9

 

[Fluff]

The data you are showing is only formatted to show hours & minutes. If you change the format to show seconds as well you will see that it does work.

+Fluff 1.xlsm
	AA	AB	AC	AD	AE
1	Date	Date clock corrected	excel format of date	time correction seconds	Corrected date
2
3	05/01/2021 08:33:41	44139	44139	3.791963	05/01/2021 08:33:45
4	05/01/2021 13:48:24			3.805253	05/01/2021 13:48:28
5	07/01/2021 14:10:25			3.927805	07/01/2021 14:10:29
6	06/02/2021 09:24:22			5.740049	06/02/2021 09:24:28
7	16/02/2021 00:54:31			6.326627	16/02/2021 00:54:38
8	17/02/2021 06:06:24			6.400608	17/02/2021 06:06:30
9	21/02/2021 09:16:51			6.651894	21/02/2021 09:16:58
10	25/02/2021 08:58:38			6.894368	25/02/2021 08:58:44
11	26/02/2021 08:58:38			6.955178	26/02/2021 08:58:44
Primary

Cell Formulas
Range	Formula
AC3	AC3	=AB3
AD3:AD5,AD9:AD11	AD3	=(AA3-$AB$3)*9/148
AE3:AE11	AE3	=AA3+(AD3/86400)

 

[Ed Harris]

Thankyou! how did I not spot that.

 

[Fluff]

You're welcome & thanks for the feedback.