Skip to main content

How to use WEEKDAY function to get last Friday in Google Sheets

Labels:

As I manage my stock investment portfolio in Google Sheets, I need to see its evolution over time, for example, in the last year. However, the computation for daily evolution is resource-consuming and might cause performance issues for the spreadsheet. As an alternative, I compute only the weekly evolution of the investment portfolio for the last year. For each week, I compute only the portfolio's value at the end of the Friday. For that, I need a Google Sheets formula to return the last Friday for a given date. This post explains how I do that with the WEEKDAY formula in Google Sheets.

Use WEEKDAY function in Google Sheets to find last Friday

Table of Contents

Concept

To find the last Friday from a date, I need to determine how many days I need to subtract from that date.

  • If the date is Saturday, the last Friday is 1 day before it.
  • If the date is Sunday, the last Friday is 2 days before it.
  • If the date is Monday, the last Friday is 3 days before it.
  • If the date is Tuesday, the last Friday is 4 days before it.
  • If the date is Wednesday, the last Friday is 5 days before it.
  • If the date is Thursday, the last Friday is 6 days before it.
  • If the date is Friday, the last Friday is 7 days before it.

In Google Sheets, the WEEKDAY function returns a number representing the day of the week for a given date. More specifically, if the type is 2, Monday is the first day of the week, and its value is 1, while Sunday is the last day of the week, and its value is 7.

I made a small schema below to explain how to calculate the number of days to subtract based on the WEEKDAY function:

How to use WEEKDAY function in Google Sheets

Formula and demo

Here is the final formula that can be applied on any date to find out the last Friday.

=TODAY()-IF(WEEKDAY(TODAY(),2)>5,WEEKDAY(TODAY(),2)-5,2+WEEKDAY(TODAY(),2))

Conclusion

In the next posts, I will explain how to compute the weekly evolution of a stock investment portfolio in Google Sheets. The formula to find last Friday for a given date presented in this post will be useful.

Disclaimer

The post is only for informational purposes and not for trading purposes or financial advice.

Feedback

If you have any feedback, question, or request please:

Support this blog

If you value my work, please support me with as little as a cup of coffee! I appreciate it. Thank you!

Support AllStacksDeveloper blog a coffee if you find the contents useful!

Share with your friends

If you read it this far, I hope you have enjoyed the content of this post. If you like it, share it with your friends!

Comments

Post a Comment

Popular posts

Compute cost basis of stocks with FIFO method in Google Sheets

Compute cost basis of stocks with FIFO method in Google Sheets

After selling a portion of my holdings in a stock, the cost basis for the remain shares of that stock in my portfolio is not simply the sum of all transactions. When selling, I need to decide which shares I want to sell. One of the most common accounting methods is FIFO (first in, first out), meaning that the shares I bought earliest will be the shares I sell first. As you might already know, I use Google Sheets extensively to manage my stock portfolio investment, but, at the moment of writing this post, I find that Google Sheets does not provide a built-in formula for FIFO. Luckily, with lots of effort, I succeeded in building my own FIFO solution in Google Sheets, and I want to share it on this blog. In this post, I explain how to implement FIFO method in Google Sheets to compute cost basis in stocks investing.
Create personal stock portfolio tracker with Google Sheets and Google Data Studio

Create personal stock portfolio tracker with Google Sheets and Google Data Studio

I have been investing in the stock market for a while. I was looking for a software tool that could help me better manage my portfolio, but, could not find one that satisfied my needs. One day, I discovered that the Google Sheets application has a built-in function called GOOGLEFINANCE which fetches current or historical prices of stocks into spreadsheets. So I thought it is totally possible to build my own personal portfolio tracker with Google Sheets. I can register my transactions in a sheet and use the pivot table, built-in functions such as GOOGLEFINANCE, and Apps Script to automate the computation for daily evolutions of my portfolio as well as the current position for each stock in my portfolio. I then drew some sort of charts within the spreadsheet to have some visual ideas of my portfolio. However, I quickly found it inconvenient to have the charts overlapped the table and to switch back and forth among sheets in the spreadsheet. That's when I came to know the existen...
How to calculate the internal rate of return (IRR) and the net present value (NPV) of a stock portfolio with Google Sheets

How to calculate the internal rate of return (IRR) and the net present value (NPV) of a stock portfolio with Google Sheets

As a long-term investor, I need to know how to evaluate the performance of my stock portfolio. A simple return on investment calculation is not a good indicator for long-term investment because it does not take into account the holding duration, and cash flows involved during that period. A return on investment of 80% after 20 years is not as impressive as it sounds after 1 year. In this post, I explain the idea of using Google Sheets to calculate the internal rate of return (IRR) and the net present value (NPV) of a stock portfolio.
Stock Correlation Analysis With Google Sheets

Stock Correlation Analysis With Google Sheets

Correlation is a statistical relationship that measures how related the movement of one variable is compared to another variable. For example, stock prices fluctuate over time and are correlated accordingly or inversely to one another. Understanding stock correlation and being able to perform analysis are very helpful in managing a stock portfolio investment. In this post, I explain in details how to perform correlation analysis among stocks in Google Sheets.
Slice array in Google Sheets

Slice array in Google Sheets

Many functions in Google Sheets return an array as the result. However, I find that there is a lack of built-in support functions in Google Sheets when working with an array. For example, the GOOGLEFINANCE function can return the historical prices of a stock as a table of two columns and the first-row being headers Date and Close. How can I ignore the headers or remove the headers from the results?
Monitor stock investment portfolio with Pivot table and GOOGLEFINANCE function in Google Sheets

Monitor stock investment portfolio with Pivot table and GOOGLEFINANCE function in Google Sheets

As an investor, it is important to know the latest state of the stock portfolio. We need to know what stocks currently owned in the portfolio, how many shares for each one, how much dividend or gain contributed so far by each stock, etc. As we have registered stock transactions in a spreadsheet with Google Sheets, we can easily have the latest update from the stock portfolio by using pivot tables and GOOGLEFINANCE function.
Demo how to use XIRR and XNPV functions of Google Sheets to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio

Demo how to use XIRR and XNPV functions of Google Sheets to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio

I have explained the idea of using Google Sheets functions to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio . The process consists mainly of three steps: Identify cash flows from transactions managed in a Google Sheets spreadsheet Choose a discount rate based on personal preferences Apply XIRR and XNPV functions of Google Sheets In this post, I demonstrate step-by-step how to apply this process to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio at 3 levels.
Manage Stock Transactions With Google Sheets

Manage Stock Transactions With Google Sheets

The first task of building a stock portfolio tracker is to design a solution to register transactions. A transaction is an event when change happens to a stock portfolio, for instance, selling shares of a company, depositing money, or receiving dividends. Transactions are essential inputs to a stock portfolio tracker and it is important to keep track of transactions to make good decisions in investment. In this post, I will explain step by step how to keep track of stock transactions with Google Sheets.