Today I learned a few tricks in Excel which are pretty fascinating.
Today my coworker need to add some delay to an existing date (more like adding the number of days processing the order on top of the order receiving date). It could work as a simple addition by adding a number to any date cell. However, my coworker insisted on using a function called WORKDAY which I have never seen before. It turned out to be such a simple concept while providing a scenario more realistic – most people don’t work during weekends 🙂
Another thing that I realized in Excel is the various ways of formatting dates. One way to extract day of the week is by simply using the format and use ddd (Wed) or dddd (Wednesday).
I recently had a challenge writing formulas to Excel file using Python. I managed to create the value of the cell as the right formula but it is not being evaluated or active when you open it up in Excel.
I was using the to_excel method of pandas dataframe. And then someone pointed me to this awesome library XlsxWriter. It supports many of the features within Excel including but not restricted to formula, chart, image and even customized format.
Here is a short snippet of code of showing how it worked just out of box.
And the output looks straightforward and satisfying.
Also, as you can see from the code, you should really try to get yourself out of the business of working with row index and column index directly. For example, whenever I think about you are going to use things like row=row+1 or i++, it reminds me of the languages like C++, Java which we should stay away from.
Here is another example of directly working with pandas dataframe using XlsxWriter as the engine.
And then, this is how the output file looks like, we have a new column called col2 that are active links. Clearly, it has been evaluated and active, when you click on it, it will link you to/jump directly to the A1 cell of sheet1. Problem solved.