Last active
July 7, 2023 07:31
-
-
Save akingdom/11dcfa28d28d09eacd4db9544729da21 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Useful Excel Spreadsheet Formulas | |
| --------------------------------- | |
| By Andrew Kingdom | |
| MIT license | |
| Shows the number of weeks covering two dates. This works across year boundaries. This is not the same as the number of weeks between two dates. | |
| [B7] 2022-11-23 Date 1 | |
| [B8] 2023-01-15 Date 2 | |
| [B9] =ROUNDUP(((B8-WEEKDAY(B8,1)+7)-(B7-WEEKDAY(B7,1)+1))/7,0) | |
| Date of the first day of the week of a given date, based on Sunday as first day. | |
| [B12] 2022-11-23 Date | |
| [B13] =B12 - WEEKDAY(B12,1) + 1 | |
| ISO8601 formatted date | |
| [B16] 2022-11-23 Date | |
| [B17] =right("000"&year(b16),4)&"-"&right("0"&month(b16),2)&"-"&right("0"&day(b16),2) | |
| Output: 2022-11-23 as text | |
| Get name of another sheet (e.g. for an 'Index' or 'About' explanatory sheet, using 'Sheet2' as name of other sheet. | |
| This does not work if referencing the current sheet, it must be referenced on another sheet. | |
| [B22] =CHOOSECOLS(TEXTSPLIT(CHOOSECOLS(TEXTSPLIT(CELL("address",'Sheet2'!$A$1),"]"),2),"!"),1) | |
| Google Sheets equivalent | |
| [B24] '=CHOOSECOLS(SPLIT(CELL("address", Sheet2!$A$1),"!"),1) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment