Default mail account in Outlook

I did a mail merge to e-mail in Outlook 2016 on Windows 10. Before i did this i ensured the default account was set correctly, but instead the e-mail went from the club’s gmail account and not our own domain.

Turns out that the default account in Outlook is frequently ignored and uses the address for the message store that currently has focus. Now annoyingly i use this every day at work, and i’m aware of the behaviour and actually like it. However, this is not so great for sending of mail merged e-mail.

On having a google around I came across this post http://www.slipstick.com/outlook/outlook-2010/multiple-accounts-and-the-default-account/ which details how to get this fixed. I’ve included the registry links after the break should this site go down. Continue reading “Default mail account in Outlook”

Advertisements

Calculating fiscal month number in Excel

This has come via this post at Mr Excel. and a response provided by Ron Coderre.

Assuming the fiscal year starts in April, the formula below will give you the fiscal month.

=MONTH(EDATE(A1,9))

The value of ‘9’ is derived by the months remaining in the year that are the new financial year. In this case there are 9 months left in the year that are the new financial year (April, May, June, July, August, September, October, November, December)

Inbox wrangling

A few months ago I started using a tickler file like system in outlook. My inbox got removed from sight and I moved e-mails from there to a ‘working folder’ a couple of times a day. 

This worked well, but there was a lot of moving e-mails about. This was annoying as there is enough metadata in outlook to enable this automatically, so after working with the system I spent some time and implemented using Outlook’s powerful (but under documented) search folders. 

  
My e-mail folders now look like that, with my work flow bring as follows. 

E-mail gets moved into the ‘unprocessed e-mail’ folder a couple of times each day. From there the e-mails are processed (GTD based – email actioned if it will take 2 minutes or less). If the e-mail will take longer a due date is set which will then have it appearing in specific folders. 

Each morning e-mail due  from yesterday gets a quick review and either marked as completed or has the due date altered, which may not necessarily be today.

So far this system is working well – it keeps my inbox clear and means I know exactly what is outstanding. 

Date tricks in Excel – Latest Month, and preceeding 12

Assuming you have your data formatted as a table (if not, why not) then you can use the following technique to see if it is the most current month for that set of date or if it is in the most recent n number of months.

For this example we assume that you have a simple list comprising of

  • Customer Name
  • Date order placed

You want to see the orders that were placed during the latest month you have data for.

The first thing will be to have a column to represent the month which is just a date format of the first of the month. This could be done in the formula later, but this reduced the complexity of the subsequent formula in that you always know that it will be the first of the month you are dealing with. Assuming you had a date in B1 (which is set to 29 June 2015), the formula to give the first of the month would be:

=DATE(YEAR(b1),MONTH(b1),1)

The date takes three parameters of year, month, and day and converts that to a date serial number, but instead of numbers we have put more functions. The year function returns the year of a date serial number as a four digit number.
The month function returns the number of the month of a date serial.
The final parameter is set to 1 – which puts the day element as the first of the month.
So with the date as 29/6/2015 the function evaluates to:

=DATE(2015,6,1)

With this column in place and called month. we then use either of the following techniques.

Working out if Current month.

To work out if the row is in the current month you can use the following formula.

=IF([@[Month]]=MAX([Month]),"Latest","Previous")

This compares the value of month in the row with the highest value in the entire month column. IF they are the same then the formula returns the string ‘Latest’ if not it returns ‘Previous’.

Working out in preceeding n months.

To work out if the value is in the preceeding n months, then use the following formula.
=IF([@[Order Date]]>=DATE(YEAR(MAX([Order Date])),MONTH(MAX([Order Date]))-13,1),"Previous 12","Older")