Please Help with this problem

[Rian_Pramana]

I have this data, 10 7 2021 10:00(d m yyyy h:mm) without "/" or "-" just space between in A2,

and 11 7 2021 10:00 in B2 I just trying this =(A2-B2)+1 but can't, i already try custom format but still

question:

1. date must have "/" or "-"?

2. can i just change my data without change it manually? i have very much data 

Please Help

[#2]

yes date should have / otherwise excel will think its text, and in text you cant us arithmatic operators

[#3]

@Rian_Pramana You mention the you have "very much data". That suggests that PowerQuery (PQ) is the solution to your problem. If you have never used it before it may be a bit overwhelming to get started. But once you master the basics, a new world will open up and it will change the way how you work with data in Excel in the future.

PQ allows you to connect to your raw data, transform and clean it and create a usable format, do calculations and create the kind of output you described. The attached file is how it could look like.

Columns A and B hold text data like in your example. The green table was created by PQ.

By the way, in your question you mentioned a formula like A-B+1. With dates/times in column B that are after the ones in column A, that didn't seem to make much sense. So, in the attached example I calculated duration as B - A. 

[#4 · Accepted solution]

@Rian_Pramana as @Riny_van_Eekelen said, using power query is the most efficient way to handle this scenario. 

One other way you may try

1. split the time to a different column using "Text to Columns", "fixed-width". You will get it as below B and C
2. replace the "space" with "/" or "-" in B
3. merge the B and C after replacing with a simple function.

A	B	C	D
In	In Date	In Time	In Combined
10 7 2021 10:10	10 7 2021	10:10	10-7-2021 10:10

Hope this helps.

[#5]

Wow thankyou so much, it works very well

[#6]

@Riny_van_Eekelen 

thankyou so much, i will try it.. good to know it

[#7]

hahahah i really confused with the proggrammer right now who give me that data format

[#8]

@Rian_Pramana 

If default date format is d/m/yyyy when

=SUBSTITUTE(SUBSTITUTE(B2, " ","/",1), " ","/",1)-
 SUBSTITUTE(SUBSTITUTE(A2, " ","/",1), " ","/",1)