-Transforming timestamps and dates

Sacha Chua Formulas may vary depending on your spreadsheet program Combining times & dates A1 1/2/2014 = A1+ B1 B12:00 PM 1/2/2014 2:00PM Splitting times & dates A1 1/2/2014 2:00PM DATE(YEAR (A1), MONTH (A1), DAY(A1)) 1/2/2014 MINUTE = Beginning of month/year A1: 12/2/2014 (can include time) DATE (YEAR (A1), MONTH (A1), 1) 2/1/2014 month = DATE (YEAR (A1), 1,1) = TIME (HOUR(A1), (A1), SECOND (A1)) 12:00PM Converting UNIX timestamp A1 1389017640 = 1/1/2014 year Day of week A1 1/6/2014

• WEEKDAY (A1, 1)

=21: Sun-Sat12 3 4 5 67S M T W Th F S2014-01-06Beginning of quarterA1/5/1/2014=DATE (YEAR (A1),FLOOR (MONTH (A1), -1,3)+1,1)4/1/2014Beginning of weekA1 1/5/2014Sun to Sat:2: Mon-Sun 1 2 3 41 2 3 4 5 6 7M T W Th F S S=

• A1-WEEKDAY (A1D+1

11/5/2014Mon to Sun:=A1-WEEKDAY (A1,2)+112/30/13appear as a number.A1/86400 + DATE (1970, 1, 1) + (-5/24) 11/6/2014 9:14 format as date / time.Greplace withyour GMT offset