Column Formulas - Date taken

[anmac1789]

How do we use excel formulas with everything columns since cells are not defined in everything?

[void]

There are no "cells" in Everything.

Instead Everything uses "properties".
You apply formulas to "properties".

In Everything, you set the formula for the entire column.

For example:

With the search:
column1:=size: add-column:column1

Each row will show the file size in column1.




With the search:
column1:=size:*8 add-column:column1

Each row will now show the file size in bits in column1.

[anmac1789]

There are no "cells" in Everything.

Instead Everything uses "properties".
You apply formulas to "properties".

For example:

With the search:
column1:=size: add-column:column1

Each row will show the file size in column1.




With the search:
column1:=size:*8 add-column:column1

Each row will now show the file size in bits in column1.

I see thanks for that information. Can everything use excel's function =unique? Which excel formulas can everything incorporate?

[void]

No, Everything doesn't support arrays yet.

Most preprocessor search functions are supported.



To find unique values:
Hold down shift and right click your column header with the desired property, under Find <property> duplicates, click Find Unique (Including first duplicated)

For example, to find unique sizes:
Hold down shift and right click the size column header, under Find size duplicates, click Find Unique (Including first duplicated)



Using the column1 property, you can combine properties which might be useful to find unique or duplicated files.
For example, to find files with duplicate size and date modified to the second:
column1:=TEXTJOIN(";",TRUE,size:,formatfiletime(dm:,"YYYY-MM-DD\THH:mm:ss")) add-column:column1
Right click the Column 1 column header and click Find Column 1 duplicates.

[anmac1789]

No, Everything doesn't support arrays yet.

To find unique values:
Hold down shift and right click your column header with the desired property, under Find <property> duplicates, click Find Unique (Including first duplicated)

For example, to find unique sizes:
Hold down shift and right click the size column header, under Find size duplicates, click Find Unique (Including first duplicated)



Using the column1 property, you can combine properties which might be useful to find unique or duplicated files.
For example, to find files with duplicate size and date modified to the second:
column1:=TEXTJOIN(";",TRUE,size:,formatfiletime(dm:,"YYYY-MM-DD\THH:mm:ss")) add-column:column1
Right click the Column 1 column header and click Find Column 1 duplicates.

everything column dupes.png (69.99 KiB) Viewed 5165 times

Id like to have your style of format for date modified but extend it to all day-cr,day-md, day-ac and 3 time-cr, time-mod, time-acc how can I do that ? The serial number is a little hard to read

[void]

continued here

[void]

Added a complete list of supported functions.

[froggie]

IF is described as

IF(x,y,z) - if x is TRUE (or 1), return y. Otherwise, return z.

the way it is working (which exactly matches the way EXCEL works) is

IF(x,y,z) - if x is TRUE (non zero), return y. If x is FALSE (exactly zero), return z.

You keep adding things that I didn't know I needed until you added them, and then I do. Wonderful.

[anmac1789]

IF is described as

IF(x,y,z) - if x is TRUE (or 1), return y. Otherwise, return z.

the way it is working (which exactly matches the way EXCEL works) is

IF(x,y,z) - if x is TRUE (non zero), return y. If x is FALSE (exactly zero), return z.

You keep adding things that I didn't know I needed until you added them, and then I do. Wonderful.

Excel is very very useful, when arrays get added watch it be a excel killer software lol

[anmac1789]

How do I add a custom column with this formula into everything?

=LET(a,BYROW(C$2:D$12,LAMBDA(d,TEXTJOIN("|",,d))),IFERROR(INDEX(UNIQUE(FILTER(a,LEFT(a,LEN(C2)+1)=C2&"|")),COUNTIF(C$2:C2,C2)),""))

and

=countif(range, 1st cell in range)

Also, lets say I have duplicate files that share common directory structure:
duplicate file 1 - C:\users\phone\android\backup\data
duplicate file 2 - C:\users\android\backup\data

How can I match the two files together by searching for directory structure starting from right to left ? It seems using excel with this will be a little complicated

[void]

What are you trying to do here?
Everything doesn't support variables or ranges yet.

How do I add a custom column with this formula into everything?

To compare the last 3 path parts, try something like:

Code: Select all

addcolumn:column1 column1:=pathcombine(filename(pathpart(pathpart(path:))),pathcombine(filename(pathpart(path:)),filename(path:)))

[anmac1789]

What are you trying to do here?
Everything doesn't support variables or ranges yet.

How do I add a custom column with this formula into everything?

To compare the last 3 path parts, try something like:

Code: Select all

addcolumn:column1 column1:=pathcombine(filename(pathpart(pathpart(path:))),pathcombine(filename(pathpart(path:)),filename(path:)))

I'm trying to match two files starting from a specified part of the path until the end of that path. For example in the above,

duplicate file 1 - C:\users\phone\android\backup\data
duplicate file 2 - C:\users\android\backup\data

starting from file 1 - start from path part 4-->end (3rd slash sign)
starting from file 2 - start from path part 3-->end (2nd slash sign)

[void]

Everything 1.5.0.1324a fixes an issue with len() returning the wrong number of UTF-16 characters.

[anmac1789]

Everything 1.5.0.1324a fixes an issue with len() returning the wrong number of UTF-16 characters.

Thank you void

[anmac1789]

How do use a column1 value for column 2 ?
For example column1 is text(date-taken:,"hh:mm:ss") but how do I use column 1 values for column 2 which is supposed to be formatted as TEXT(ABS($time-created-column1),"[ss]") ???

[void]

You can reference the column 1 property with column1:

Everything doesn't support arithmetic on dates/times.
I will consider support for arithmetic on dates/times in a future release.
Arithmetic on filetimes is supported.

please try:

date-created: date-taken: column2:=formatduration(ABS((dc:%864000000000)-(datetaken:%864000000000)),"hh:mm:ss")

Include date-created: date-taken: in your search to find only files with a valid date-created and a valid date-taken.
%864000000000 will convert a filetime to the time part only.
Use formatduration() to make the results more readable.

[anmac1789]

You can reference the column 1 property with column1:

Everything doesn't support arithmetic on dates/times.
I will consider support for arithmetic on dates/times in a future release.
Arithmetic on filetimes is supported.

please try:

date-created: date-taken: column2:=formatduration(ABS((dc:%864000000000)-(datetaken:%864000000000)),"hh:mm:ss")

Include date-created: date-taken: in your search to find only files with a valid date-created and a valid date-taken.
%864000000000 will convert a filetime to the time part only.
Use formatduration() to make the results more readable.

do you mean :

column2:=formatduration(TEXT(ABS((dc:%864000000000)-(datetaken:%864000000000))),"hh:mm:ss")

[void]

TEXT() will work on absolute filetimes, not relative/durations.

Please use formatduration() to format a relative/duration filetime.

Please try:
column2:=formatduration(ABS((dc:%864000000000)-(datetaken:%864000000000)),"hh:mm:ss")

[anmac1789]

TEXT() will work on absolute filetimes, not relative/durations.

Please use formatduration() to format a relative/duration filetime.

Please try:
column2:=formatduration(ABS((dc:%864000000000)-(datetaken:%864000000000)),"hh:mm:ss")

So, in this case, even without text(), relative/duration still give the appropriate time in seconds because its formatted as "hh:mm:ss" ??

[void]

Yes, if you need more resolution please try the following:

column2:=formatduration(ABS((dc:%864000000000)-(datetaken:%864000000000)),"hh:mm:ss.SSSSSSS")

This will show the full 100-nanosecond resolution.

formatduration() format syntax

[anmac1789]

Yes, if you need more resolution please try the following:

column2:=formatduration(ABS((dc:%864000000000)-(datetaken:%864000000000)),"hh:mm:ss.SSSSSSS")

This will show the full 100-nanosecond resolution.

formatduration() format syntax

Thank you for this information. Going down my list I came across something like this:

Inkedduplicates.jpg (187.2 KiB) Viewed 4553 times

is there a way to find the difference in time between two files with the same filename, size and days?

[void]

Currently, no.

Column formulas can only access properties for the current file.



The following search may help identify files with the same name, size and a different date-taken:

dupe:name;size;!date-taken

You would then need to export to Excel and calculate differences between cells.

[void]

moved to Column Formulas - Date taken topic.

Column Formulas