Subtract dates

[smide]

Hello.

I have a spreadsheet with text cells for dates (column B) in the format dd.mm.yyyy - hh:mm
I need formula to subtact two dates. Result in minutes.

example.

	A​	B​	C​
1​	Date 1	10.04.2022 - 22:00​
2​	Date 2	11.04.2022 - 01:30​
3​	result (Date 2 - Date 1)	210​	​

 

[Phuoc]

Book1
	A	B
1	Date 1	10.04.2022 - 22:00
2	Date 2	11.04.2022 - 01:30
3		210
Sheet1

Cell Formulas
Range	Formula
B3	B3	=(DATE(MID(B2,7,4),MID(B2,4,2),LEFT(B2,2))+RIGHT(B2,5)-DATE(MID(B1,7,4),MID(B1,4,2),LEFT(B1,2))-RIGHT(B1,5))*1440

 

[Alex Blakenburg]

The below worked for me but assumes that the text date is in your Local Format eg displaying dd.mm.yyyy and your local format is dd/mm/yyyy.
@Phuoc solution would work regardless eg even if your Local Format was mm/dd/yyyy

=(VALUE(SUBSTITUTE(SUBSTITUTE(B2,"- ",""),".","/"))-VALUE(SUBSTITUTE(SUBSTITUTE(B1,"- ",""),".","/")))*1440

 

[Tom.Jones]

Or

=(SUBSTITUTE(B2," - "," ")-SUBSTITUTE(B1," - "," "))*1440 ===> 210

where in B1=10.04.2022 - 22:00 and in B2=11.04.2022 - 01:30