I’m a big believer in productivity and personally, trying to manage my emails as efficiently as possible is the #1 way to do this at work. I manage my emails in many different ways and always try to keep the number of emails in my inbox to a minimum. One thing that I like to do is sending postponed emails to myself and a few people in my team. Why? Because in general it’s the most efficient way to make sure no one forgets about something that needs to be done and it’s easy for anyone to reply.

I used to do this by going into Outlook and sending emails from there while going into the options and selecting a different “sending date”. It turned out to be very time consuming though and I tried to get this done in excel since I know there are many ways to write VBA macros that communicate together. Here is what I ended up getting:

The interface that I use to generate the emails is fairly simple:

Once I enter the details, I press send and the email is added to my outbok where it will remain until that specified time and date appears.

I could also write macros that would generate many emails if there was a simple order. For example if I needed to send the same email every day or every month, I could do it using this file as a starting point.

It has truly saved me many hours of time.

As always, you can download the spreadsheet here!

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.

o explain very briefly, a few months ago I made a purchase with my parents. They paid the condo in cash and I was left with my part that I would pay them 5 years later without interest (good deal hey?). So I’ve been saving money every month and investing it in a few stocks. I use a very simple spreadsheet to calculate how much I’ll need to save every month. If my money was invested in a fixed rate account, that number would remain the same. But since my investments values increase and decrease every month, the number actually changes.

I will use the PMT function to calculate what I need. In order to get started I will need:

Rate = my expected return (I will use a monthly return since I’m looking for a monthly amount)
Nper = how many months between now and me paying that amount back
PV = how much I currently have in that account
FV = how much I’ll need to pay at the end

Rate: I will assume a 7% annual return. To get the monthly return, I’ll simply do:

=1.07^(1/12)-1

Nper = 47 months
PV = $13,000
FV = -$50,000

You can see both the table, formula and answer here:

Simple enough? It gets better. Since I linked my PV amount to a page where I get live stock quotes, that numbers can become “live”. You can download the spreadsheet here.

Once in a while I get asked some more complex nested if/and questions. In almost all cases, it is all about defining the problem in a simple way and building the formula step by step. Here is one question I received a couple of weeks ago:

If I redefine this problem. I have 4 values. In order for the entire problem to be true, 2 conditions must be met. And each of those conditions can be accomplished with 1 of 2 possible values. So let’s go ahead and try this out:

I will start at the end. If A=C or C=D true, that condition will be met. Here we go:

=IF(OR(C2=C4,C4=C5),1,0)

Basically, if one of the conditions is met, the formula will give me 1. I will do the same thing for that other part.

=IF(OR(C2=C3,C3=C5),1,0)

Now, I must combine these 2. Basically, if the sum of both is 2, then the overall condition is met. If not, it is false. Here is how I’ll do it:

=IF(IF(OR(C2=C4,C4=C5),1,0)+IF(OR(C2=C3,C3=C5),1,0)=2,”True”,”False”)

You can see the result which is false of course:

If I change B and C to match A, I should get a “true”

And here I go:

Of course, this could be done in many different ways but I just wanted to show another way. Hopefully this helps!