Derrick.Blarg

Tag: nouns

  • Get First Date of Week in Google Sheets, When the Week of the Year is Related to the Row Number

    Get First Date of Week in Google Sheets, When the Week of the Year is Related to the Row Number

    *phew* what a title

    For a recent project, I wanted to set up a Google Sheet that tracked something daily/weekly. I wanted one column to show the starting day of the week to help keep me on track, and I ended up with this:

    I used ChatGPT to help me make the formula to determine the first day of the week, indexed to the current row:

    =DATE(YEAR(TODAY()), MONTH(DATE(YEAR(TODAY()),1,1)+(ROW()-2)*7-WEEKDAY(DATE(YEAR(TODAY()),1,1))+1), DAY(DATE(YEAR(TODAY()),1,1)+(ROW()-2)*7-WEEKDAY(DATE(YEAR(TODAY()),1,1))+1))

    This is how ChatGPT explains how the function works:

    This Google Sheets function returns the date that corresponds to the first day of the week for a given row number. The row number is specified by the ROW() function, which returns the row number of the current cell.

    The function uses the following steps to calculate the date:

    1. It uses the YEAR(TODAY()) function to get the current year.
    2. It uses the DATE(YEAR(TODAY()),1,1) function to get the first day of the current year (e.g. January 1).
    3. It uses the WEEKDAY(DATE(YEAR(TODAY()),1,1)) function to get the day of the week of the first day of the year as a number (e.g. 1 for Monday, 2 for Tuesday, etc.).
    4. It uses the (ROW()-2)*7 expression to calculate the number of days to skip from the first day of the year to get to the desired week. For example, if the ROW() function returns 2, then the expression will evaluate to 0, which means that no days need to be skipped. If the ROW() function returns 3, then the expression will evaluate to 7, which means that one week needs to be skipped.
    5. It uses the DATE(YEAR(TODAY()),1,1)+(ROW()-2)*7-WEEKDAY(DATE(YEAR(TODAY()),1,1))+1 expression to calculate the date that corresponds to the first day of the week for the given row number.
    6. It uses the MONTH(DATE(YEAR(TODAY()),1,1)+(ROW()-2)*7-WEEKDAY(DATE(YEAR(TODAY()),1,1))+1) function to get the month of the calculated date.
    7. It uses the DAY(DATE(YEAR(TODAY()),1,1)+(ROW()-2)*7-WEEKDAY(DATE(YEAR(TODAY()),1,1))+1) function to get the day of the month of the calculated date.
    8. It uses the DATE() function to combine the year, month, and day into a single date.

    For example, if the ROW() function returns 2 and the current year is 2022, the function will return the date January 3, 2022. If the ROW() function returns 3 and the current year is 2022, the function will return the date January 10, 2022.

    It took a lot of trial and error, and manual adjustments to get the function to work, and it was really fun to play with ChatGPT and work together to get this working. While I could have trial and errored myself to the end functionality eventually, this did end up saving me quite a bit of time.

    If you, random Internet stranger, want to use this function yourself, but your rows aren’t lined up the same as mine, you’ll just need to adjust the (ROW()-2) type areas to change the row offset you’re currently starting at.

  • iOS Reminders to Habitica To Do’s via IFTTT

    iOS Reminders to Habitica To Do’s via IFTTT

    After digging around for a while trying to see how I could link up iOS’s Reminders with Habitica‘s To Do’s to help keep me organized, I finally found an easy way through IFTTT.

    This works easily because Habitica offers a wonderful API💥

    Specifically we’re looking at the “Create a new task belonging to the user” API endpoint:

    https://habitica.com/api/v3/tasks/user

    With this, we’ll need to make a POST request with some special headers to authenticate and then a body payload made of JSON:

    Headers:

    X-Client: my-user-id-IFTTTiOSRemindersSync
X-API-User: my-user-id
X-API-Key: my-api-keyCode language: HTTP (http)

    Body:

    {
	"text": "{{Title}}",
	"type": "todo",
	"notes": "{{Notes}} (Imported from iOS Reminders via IFTTT)"
}Code language: JSON / JSON with Comments (json)

    From here, IFTTT will fill in the title, notes, and ship it off to Habitica for me to check off for some sweet XP!

  • Cool WordPress Plugins: Embed Extended

    Cool WordPress Plugins: Embed Extended

    Embed Extended – Embed Maps, Videos, Websites, Source Codes, and more

    If you saw my last post, you may have noticed some cool embeds. These are coming from the Embed Extended plugin. This plugin takes OpenGraph data and treats it more like oEmbed data for WordPress. It works great with the block editor as well!

Close

Ad-Blocker Not Detected 🛑

Consider installing a browser extension that blocks ads and other malicious scripts in your browser to protect your privacy and security, conserve your expensive data plan, and cut your online carbon footprint. Learn more.