Excel

Subtract 2 hours from a datetime

------------------------------------------------------------------------------------------------

Substitute a ^ with a line break in a cell

Open replace dialog and replace ^ with CTRL+j

------------------------------------------------------------------------------------------------

Subtract 2 dates and show difference in hours and minutes

=TEXT((B3-B2);"[s]:dd")

(Note: if you omit [], hours will be cut on every 24 hours)

------------------------------------------------------------------------------------------------

Substitute last character in a line:

Example: Substitue last (.) with (€)

=SUBSTITUTE(A1;".";"€";LEN(A1)-LEN(SUBSTITUTE(A1;".";"")))

------------------------------------------------------------------------------------------------

TimeStamp to Human Time with GMT+3

=(((A1/60)/60)/24)+DATE(1970;1;1)+(3/24)

=(((A1/60000)/60)/24)+DATE(1970;1;1)+(3/24)

Comments to cell

http://www.extendoffice.com/documents/excel/765-excel-convert-comments-to-cells.html#a1

------------------------------------------------------------------------------------------------

VLOOKUP kullanımı

-------------------------------------------------------------------------------------------------

The dollar sign 'anchors' a column, row or both. Examples:

$a1

a$1

$a$1

-------------------------------------------------------------------------------------------------

Excel işe random şifre vs üretme

=RANDBETWEEN(10,99)&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(10,99)

=RANDBETWEEN(10;99)&CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(65;90))&RANDBETWEEN(10;99)

=RASTGELEARADA(10;99)&DAMGA(RASTGELEARADA(65;90))&DAMGA(RASTGELEARADA(65;90))&RASTGELEARADA(10;99)

-------------------------------------------------------------------------------------------------

Excel için GetComment plugin'im

- Nasıl yüklenir: File -> Options -> Add-ins -> Go -> Browse

-------------------------------------------------------------------------------------------------

How to concatenate rows into one cell based on group in via Power Query in Excel 2016

Select table area
Data Tab -> Get&Transform Section -> From table
Enter similar formula in the new window: = Table.Group(Source, {"Country"}, {{"Cities", each Text.Combine([City], ", "), type text}})

Google Sites

Report abuse