Time difference between date taken and time values

[anmac1789]

I have a question regarding using textsplit, match and exact functions. So, Im trying to find the time difference in seconds from the time portion of date taken and the time created, time modified and time accessed. I've tried using match, exact but somehow it doesn't seem to work. Please check my code. Thank you.

time.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Size	Day Created	Day Modified	Day Accessed	Time Created	Time Modified	Time Accessed	Date Created	Date Modified	Date Accessed	Date Taken
2	20220727_165105.jpg	6734307	27 Jul 22	27 Jul 22	27 Jul 22	16:51:05	16:51:06	16:51:06	2022-07-27 16:51:05	2022-07-27 16:51:06	2022-07-27 16:51:06	2022-07-27 16:51:05
3	20220727_165105.jpg	6734307	27 Jul 22	27 Jul 22	27 Jul 22	16:51:05	16:51:05	16:51:05	2022-07-27 16:51:05	2022-07-27 16:51:05	2022-07-27 16:51:05	2022-07-27 16:51:05
4
5	=TEXTSPLIT(L2," ")
6	2022-07-27	16:51:05			01
7
8	=EXACT(B6,B6)
9	TRUE
10
11	=MATCH($G2,$G$2:$G$3,0)
12	1
13	2
Sheet1

Cell Formulas
Range	Formula
A6:B6	A6	=TEXTSPLIT(L2," ")
E6	E6	=TEXT(G2-F2,"[ss]")
A9	A9	=EXACT(B6,B6)
A12:A13	A12	=MATCH($G2,$G$2:$G$3,0)
Dynamic array formulas.

 

[etaf]

I may be wrong here , and only recently used textsplit -
BUT L2 is not text its a number

so in the cell will be the number 44769.7021412037 - which when formatted to DD/MM/YYYY HH:MM - it displays a date and time
BUT it is not a text with DD/MM/YYYY [space] HH:HH

so it can not split - it least not that i can.... on my version of excel

to get the date
INT(L2)
will give the date

for time
L2-INT(L2)

just showing L column - and same in M - formatted as general and date/time

format as general and you will see the numbers

the decimal part is the time section of the cell

Book4
	L	M
1	Date Taken
2	44769.70214	7/27/22 16:51
3	44769.70214	7/27/22 16:51
4		1/0/00 0:00
5	0.702141204	1/0/00 16:51
6	44769	7/27/22 0:00
Sheet1

Cell Formulas
Range	Formula
M2:M6	M2	=L2
L5	L5	=L2-INT(L2)
L6	L6	=INT(L2)

 

[anmac1789]

I may be wrong here , and only recently used textsplit -
BUT L2 is not text its a number

so in the cell will be the number 44769.7021412037 - which when formatted to DD/MM/YYYY HH:MM - it displays a date and time
BUT it is not a text with DD/MM/YYYY [space] HH:HH

so it can not split - it least not that i can.... on my version of excel

to get the date
INT(L2)
will give the date

for time
L2-INT(L2)

just showing L column - and same in M - formatted as general and date/time

format as general and you will see the numbers

the decimal part is the time section of the cell

Book4
	L	M
1	Date Taken
2	44769.70214	7/27/22 16:51
3	44769.70214	7/27/22 16:51
4		1/0/00 0:00
5	0.702141204	1/0/00 16:51
6	44769	7/27/22 0:00
Sheet1

Cell Formulas
Range	Formula
M2:M6	M2	=L2
L5	L5	=L2-INT(L2)
L6	L6	=INT(L2)

When I type 27/07/2022 04:51 into a cell, I only get that text, the format can't be changed. But when I type 44769.7021412037 then it can be changed. Why won't it work the first way?

When I format as general I get #VALUE!

Even when textsplit is used on cell L2, the date portion can't be formatted into another format, because it's part of an array ? So then whats the workaround for that ?

 

[etaf]

ok,
entered into a cell
usually Excel would recognise that as a date and change to show date/time - but underlaying would be a number
possibly the way excel is seeing the date - not sure why

what country are you in and using excel - possibly a pc settings


Also the text is justified to the left and number to the right

as shown

dont know if this helps

sort.xlsx
	A	B
1	TEXT	DATE
2	19/10/22 21:50	10/19/22 21:50
3	19/10/22 21:50	44853.90972
4
5
6	Text split - A2
7	19/10/22	21:50
8
9
10	text split B2
11	44853.9097222222
Sheet1

Cell Formulas
Range	Formula
A3:B3	A3	=A2
A7:B7	A7	=TEXTSPLIT(A2," ")
A11	A11	=TEXTSPLIT(B2," ")
Dynamic array formulas.

 

[anmac1789]

.

ok,
entered into a cell
usually Excel would recognise that as a date and change to show date/time - but underlaying would be a number
possibly the way excel is seeing the date - not sure why

what country are you in and using excel - possibly a pc settings


Also the text is justified to the left and number to the right

as shown

dont know if this helps

sort.xlsx
	A	B
1	TEXT	DATE
2	19/10/22 21:50	10/19/22 21:50
3	19/10/22 21:50	44853.90972
4
5
6	Text split - A2
7	19/10/22	21:50
8
9
10	text split B2
11	44853.9097222222
Sheet1

Cell Formulas
Range	Formula
A3:B3	A3	=A2
A7:B7	A7	=TEXTSPLIT(A2," ")
A11	A11	=TEXTSPLIT(B2," ")
Dynamic array formulas.

This is the version I am using currently and I am in Ontario, Canada. This is very strange, I dont know what to do. Even if the original data had 10/19/22 21:50, splitting it would generate 10/19/22 and 21:50 but the date can't be formatted? isn't that a bit wierd...

 

[etaf]

whats the default date format used in canada on the PC
Hows it shown on the PC date time - window

IN UK and
my default system format is DD / MM /YYYY

so if i enter 11/27/22 - then thats not recognised as a date - because cant have 27 th MONTH
but if i enter

27/11/22 - then its seen as a date

could it be that ?

 

[anmac1789]

whats the default date format used in canada on the PC
Hows it shown on the PC date time - window

IN UK and
my default system format is DD / MM /YYYY

so if i enter 11/27/22 - then thats not recognised as a date - because cant have 27 th MONTH
but if i enter

27/11/22 - then its seen as a date

could it be that ?

The default date format is yyyy-mm-dd

 

[etaf]

and when you enter

in this order

2022-10-19

is that a date or text

 

[anmac1789]

and when you enter

in this order

2022-10-19

is that a date or text

that becomes right-aligned after I press enter

 

[etaf]

so is recognised as a date and can be formatted into any format

i suspect this is the issue
When I type 27/07/2022 04:51 into a cell, I only get that text,
you are entering a date format

27/07/2022 - but your excel system is expecting YYYY-MM-DD
and so it assumes its TEXT and NOT a number

like my system does when trying to enter MM/DD/YY
if i enter
10/19/22 - it right justifies and nolonger a real date BUT text - because excel is expecting a MONTH and 19 is NOT a month and so it must be a date
like if i wanted to enter 1st June 22
01/07/22
but if i entered it in that format assuming it should show 7th Jan - as a US date MM/DD
it will be seen as a date - BUT the wrong date

see this a lot with US systems outputing into excel - well a lot of dates are real - BUT wrong and others are TEXT
a couple of questions even yesterday on the forum because of that issue

so if its seen as text , as i showed
TEXTSPLIT works
BUT not when its entered and recognised as a date

I dont feel i have explained that well