DATEVALUE to force Excel entries into the Date format

Don't miss that you can use the DATEVALUE function in Excel to correctly format dates entered in a column, if selecting the cells and right clicking and selecting Format Cells and picking the date format on the Number tab doesn't do the trick.

In this example, a formula has been entered in column D to create a date based in numbers listed in the columns to the left. Even though column D is formatted for dates in the MM/DD/YYYY format, and the value of the formula has been pasted into the cells. The date can be correctly formatted by clicking into a cell, but we don't want to have to do this for the entire range.

Using DATEVALUE in a column to the right (just referencing the cells with the dates) will transform the dates to the correct format - or at least those dates that have not already been converted.

vba code to unprotect Excel worksheets

On the Review tab of Excel, you will find the option to password protect a worksheet. [Note that if you choose the option to protect the workbook, this will only prevent worksheets and other structural elements of the Excel file from being changed or removed. It will not prevent the data on a worksheet from being copied and edited.} It's not uncommon to come across Excel files in document productions that have password protected worksheets. When you try to select data on the worksheet, you'll get this message:

Excel does not use strong encryption, and most passwords can be cracked using the VBA code posted here, and copied below. The macro will actually change the password to a sequence of As and Bs, and not reveal the actual password. The new password will be displayed in a dialog box. Click OK, and then the worksheet will be fully editable.

I tested this macro tonight on a workbook protected with Excel 2019 using a four-digit number, a dictionary word, a short phrase, and an eight character alphanumeric code and it cracked each one in seconds.

Sub PasswordBreaker()

'Breaks worksheet password protection.

Dim i As Integer, j As Integer, k As Integer

Dim l As Integer, m As Integer, n As Integer

Dim i1 As Integer, i2 As Integer, i3 As Integer

Dim i4 As Integer, i5 As Integer, i6 As Integer

On Error Resume Next

For i = 65 To 66: For j = 65 To 66: For k = 65 To 66

For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66

For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66

For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _

Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _

Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

If ActiveSheet.ProtectContents = False Then

MsgBox "Password is " & Chr(i) & Chr(j) & _

Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _

Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

Exit Sub

End If

Next: Next: Next: Next: Next: Next

Next: Next: Next: Next: Next: Next

End Sub

DAT-daddy to parse by ¶ and þ in Excel

ENF Discovery has a great add-in for Excel that can help you parse out fields in Concordance .dat load files. Concordance load files use pilcrows (¶) and thorns (þ) to separate out different metadata fields. The pilcrow designates a column; thorn a quotation mark; and ® a new line.

It can be tricky to separate the fields in the .dat file into separate columns in an Excel workbook. DAT-daddy does the work for you. It installs as an add-in for Excel. A new tab will be created in the menu. DAT-daddy lets you customize the delimiters.

The numerous fields of the .dat file are effortlessly separated into new columns.