I’ve already discussed the issues associated with working with dates in Excel. It’s much harder than it should be. Unfortunately, that is also true about working with time. In a way, I can understand that excel can’t always guess what we’re trying to do. It should be a bit better at it though. Yesterday, I got a question from a reader that a simple question. He needed to buy 1000 shares of Microsoft done evenly throughout the day. It’s more commonly known as a TWAP (time weighted average price). He was trying to easily see if the program had bought enough or perhaps too much at multiple points during the day.

I told him I’d build a simple spreadsheet but it proved a bit more difficult than expected. Why? It’s mostly about cell formatting. Here is what I built initially:

The first part is easy. My first task is finding the number of minutes between the start and end. I need that to determine what quantity needs to be executed depending on the time. I will use 2 functions to achieve this task. Basically, I need to translate the 6:30 into minutes.

How? I will add the number of hours x 60 to the number of minutes. Unfortunately, here is the result:

However, I then simply changed the format of that cell to numbers and got this:

So every minute I must execute the total qty/mins. In this case, it is 1000/390.

Then, I needed to determine for each time on the left how much time had lapsed since the start time. How? It’s a similar principle. For C5 I will use:

=MINUTE(B5)+HOUR(B5)*60-MINUTE($J$4)-HOUR($J$4)*60

However as you can see the result isn’t good:

Why? Because the time is not being treated correctly. So I will select the entire B column and right click for cell format, I then used this format:

I now have the number of minutes between that time and the start. I can now simply multiply that by the qty/minute and I’ll know how much I need to have executed:

As you can see, I also added the use of “Max” and “round”. The round is simply to avoid having decimals while I use the max because if I change my start time to 10:00 AM, I want to avoid having negative quantities.

You can of course download the spreadsheet here.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************