Excel
Jump to navigation
Jump to search
Interest Rates
Value in Range
=IF(COUNTIF(range,value),"Yes","No")
=IF(COUNTIF(range,value)>1,"Yes","No")
ref: [1]
cell contains substring
=ISNUMBER(SEARCH("Some Text", A5))
=if(ISNUMBER(search(J4,K4)),"","BAD")
ref: [2]
Convert unix timestamp to date time string
A1 = unix time stamp B1 = 25569 epoc shift (days since 1/1/1900 and 1/1/1970 ="1/1/1970"-"1/1/1900"+1 C1 = -6 hours away from GMT = ( A1 / 1000 + B1 * 86400 + C1 * 3600 ) / 86400
Sample:
1537822800000 -> 09-24-18 15:00:00
Count if Checked
=SUMPRODUCT(($D$2:$D$15=TRUE)*$C$2:$C$15)
=SUMPRODUCT(($D$2:$D$15="X")*$C$2:$C$15) Column D has "X" Column C has $ values
https://www.extendoffice.com/documents/excel/2460-excel-sum-count-checkboxes.html
Split by delimiter
Split a single column that has a delimiter
"Smith, John"
=LEFT(A2, SEARCH(",",A2,1)-1) # Smith =RIGHT(A2, LEN(A2)-SEARCH(",",A2,1)-1) # John
Ref [3]