Excel Formulae

Excel Formulae 

Remove zeros from Excel 

0;-0;;@ 

Convert Date to month: 

=DATE(YEAR(A2),MONTH(A2),1) 

Find Job Location: 

=IF((D2="VACATION"),IF(F2<>"VACATION",F2,IF(H2<>"VACATION",H2,J2)),IF(D2="",F2,D2))

Roundup date to Next month: 

=IF(AND(DAY(A2)>=1,DAY(A2)<25),DATE(YEAR(A2),MONTH(A2),1),DATE(YEAR(A2),MONTH(A2)+1,1)) 

where A2=Date to be rounded up to next month 

Number 25 could be changed to the desired roundup days

 

Find if Duplicates: 

=countif(a:a,a2)>1 

Count number of Duplicates occurances(index): 

=countif(a$2$:a2,a2) 

Count Duplicates: 

=countif(a:a,a2) 

Show the last saved date: 

VBA Code 

Sub getChangeInfo() 

Dim lastAuth, lastSave 

lastSave = ActiveWorkbook.BuiltinDocumentProperties(12) 

ActiveSheet.Range("A1") = "Last Saved: " & lastSave 

End Sub 

Save sheets sepertely from Excel Book: 

VBA Code 

Sub CreateWorkbooks() 

Dim wbDest As Workbook 

Dim wbSource As Workbook 

Dim sht As Object 

Dim strSavePath As String 

On Error GoTo ErrorHandler 

Application.ScreenUpdating = False 

strSavePath = "C:\Temp\" 

Set wbSource = ActiveWorkbook 

For Each sht In wbSource.Sheets 

sht.Copy 

Set wbDest = ActiveWorkbook 

wbDest.SaveAs strSavePath & sht.Name 

wbDest.Close 

Next 

Application.ScreenUpdating = True 

Exit Sub 

ErrorHandler: 

MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "." 

End Sub