# Excel time calculation and formatting

It’s actually really simple, but for some reason I just keep having to look up some of these… so… finally… note to self…

Calculations Time to decimal `=X+Y``=X+N``=Y-X`Etc. `12:45 - 9:55 = 2:50` `=X*24` `2:50 * 24 = 2.83` `=X/24` `2,83 / 24 = 2:50` `d h:mm` `11 19:20` `[h]:mm` `283:20` `[m]` `17000`

Regarding the formatting, without the brackets, it formats as regular time (`total hours % 24`). So, for example, 2 hours and 50 minutes multiplied by 100; with a regular time format (`h:mm`) it displays as 19:20; using brackets (`[h]:mm`) you get the correct 283:20, which is 11 days 19 hours and 20 minutes (hence the 19:20 from `h:mm`).

# JavaScript: In-browser export to CSV

This function will turn an array of data into a CSV file and get the browser to “download” it. It should also be wrapping and escaping values properly and, because of the BOM, be read correctly when opened in e.g. Excel.

It’s written in TypeScript (`"lib": ["ES2017", "DOM"]`), but should be easy to “downgrade” to regular JS if needed…

type GetValue <T> = <I extends T>(item: I) => any;
type FieldName <T> = keyof T;

export interface Columns<T> {
}

const COLUMN_SEPARATOR = ';';
const ROW_SEPARATOR = '\r\n';
const UNICODE_BOM = '\uFEFF';

const wrapValue = (value: string) => `"\${value}"`;
const escapeValue = (value: string) => (value || '').replace(/"/, '""');

const makeHeaderLine = <T> (columns: Columns<T>) => Object
.keys(columns)
.map(escapeValue)
.map(wrapValue)
.join(COLUMN_SEPARATOR);

const makeItemLine = <T> (columns: Columns<T>, item: T) => Object
.values(columns)
.map(field => typeof field === 'function' ? field(item) : item[field])
.map(String)
.map(escapeValue)
.map(wrapValue)
.join(COLUMN_SEPARATOR);

export const exportToCsv = function <T> (data: T[], columns: Columns<T>, filename: string): void {
const rows = [];

for (const item of data) {
rows.push(makeItemLine(columns, item));
}

const csv = UNICODE_BOM + rows.join(ROW_SEPARATOR);

};

## Usage

const users: User[] = [{id: 1, name: 'Alice', isCool: true}, ...];
const columns: Columns<User> = {
'Id': 'id',
'Name': 'name',
'Is cool': user => user.isCool ? 'Yes' : 'No',
};
exportToCsv(users, columns, 'users.csv');

# Excel: Function for nslookup in worksheet

Just a copy of a function I found in a blog post for doing nslookup calls in a worksheet, in case I need it again in the future. I’ve changed the return value for failing lookups to a blank string, “”, rather than “NotFound”, but otherwise it’s unchanged.

Usage, given an IP in cell A1, to get the hostname: `=NSLookup(A1; 2)`

Public Function NSLookup(lookupVal As String, Optional addressOpt As Integer) As String
Const NAME_LOOKUP = 2
Const AUTO_DETECT = 0

'Skip everything if the field is blank
If lookupVal <> "" Then
Dim oFSO As Object, oShell As Object, oTempFile As Object
Dim sLine As String, sFilename As String
Dim intFound As Integer
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oShell = CreateObject("Wscript.Shell")

'Regular Expressions are used to complete a substring match for an IP Address
'If an IP Address is found, a DNS Name Lookup will be forced
ipLookup = FindIP(lookupVal)
If ipLookup = "" Then
Else
lookupVal = ipLookup
End If
'Do a regular expression substring match for an IP Address
lookupVal = FindIP(lookupVal)
End If

'Run the nslookup command
sFilename = oFSO.GetTempName
oShell.Run "cmd /c nslookup " & lookupVal & " > " & sFilename, 0, True
Set oTempFile = oFSO.OpenTextFile(sFilename, 1)
Do While oTempFile.AtEndOfStream <> True
cmdStr = cmdStr & Trim(sLine) & vbCrLf
Loop
oTempFile.Close
oFSO.DeleteFile (sFilename)

'Process the result
intFound = InStr(1, cmdStr, "Name:", vbTextCompare)
If intFound = 0 Then
NSLookup = ""
Exit Function
ElseIf intFound > 0 Then
'TODO: Cleanup with RegEx
loc2 = InStr(loc1, cmdStr, vbCrLf, vbTextCompare)
nameStr = Trim(Mid(cmdStr, loc1 + 8, loc2 - loc1 - 8))
loc1 = InStr(intFound, cmdStr, "Name:", vbTextCompare)
loc2 = InStr(loc1, cmdStr, vbCrLf, vbTextCompare)
nameStr = Trim(Mid(cmdStr, loc1 + 5, loc2 - loc1 - 5))
End If
End If
NSLookup = nameStr
Else
NSLookup = "N/A"
End If
End Function

Function FindIP(strTest As String) As String
Dim RegEx As Object
Dim valid As Boolean
Dim Matches As Object
Dim i As Integer
Set RegEx = CreateObject("VBScript.RegExp")

RegEx.Pattern = "\b(?:\d{1,3}\.){3}\d{1,3}\b"
valid = RegEx.test(strTest)
If valid Then
Set Matches = RegEx.Execute(strTest)
FindIP = Matches(0)
Else
FindIP = ""
End If
End Function

Source: jayteknews.blogspot.no