Managing Time In Excel

Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

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


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

One Feedback on "Managing Time In Excel"

ebooks

I am not positive the place you’re getting your info,
however great topic. I must spend a while studying more or understanding more.

Thanks for fantastic information I used to be searching for this information for my mission.



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.