Subtract dates

[smide]

Hello.




In cell A1 I have current date created with formula =today() eg. 3-Oct-2019.
In the same column starting from cell A3 (A3:A600) I have dates and time in format dd.mm.yyyy - hh:mm (example. 04.10.2019 - 21:00).


All those cells (A3:A600) are text cells. I need to subtract all those dates (A3:A600) and current date. Result(s) should be the difference expressed in days (column B).


example.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]

3-Oct-2019​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]30.09.2019 - 17:30[/TD]
[TD="align: center"]-3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]01.10.2019 - 22:00[/TD]
[TD="align: center"]-2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]02.10.2019 - 16:00[/TD]
[TD="align: center"]-1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: center"]02.10.2019 - 21:00[/TD]
[TD="align: center"]-1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: center"]03.10.2019 - 12:00[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: center"]04.10.2019 - 16:45[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: center"]05.10.2019 - 13:00[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

​

 

[FormR]

Hi, you may need to format the cells that contain the formula as general or number.

Excel 2013/2016
	A	B
1
2	03-Oct-19
3
4	30.09.2019 - 17:30	-3
5	01.10.2019 - 22:00	-2
6	02.10.2019 - 16:00	-1
7	02.10.2019 - 21:00	-1
8	03.10.2019 - 12:00	0
9	04.10.2019 - 16:45	1
10	05.10.2019 - 13:00	2
Sheet1

Cell Formulas
Range	Formula
A2	=TODAY()
B4	=SUBSTITUTE(LEFT(A4,10),".","/")-$A$2

 

[smide]

Tried everything but unfortunately I'm getting only error (#VALUE!) as a result...

 

[FormR]

Tried everything but unfortunately I'm getting only error (#VALUE!) as a result...

Let's try a method that is not susceptible to regional date/time settings.

Excel 2013/2016
	A	B
1	03/10/2019
2
3	30.09.2019 - 17:30	-3
4	01.10.2019 - 22:00	-2
5	02.10.2019 - 16:00	-1
6	02.10.2019 - 21:00	-1
7	03.10.2019 - 12:00	0
8	04.10.2019 - 16:45	1
9	05.10.2019 - 13:00	2
Sheet1

Cell Formulas
Range	Formula
B3	=DATE(MID(A3,7,4),MID(A3,4,2),LEFT(A3,2))-$A$1

 

[smide]

Works great! Thank you so much.