THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Derive fiscal week using Excel

I had to do something in Excel today that might be useful to other folks so sharing it here.

I've got a A4 sheet sellotaped to my desk that tells me the start and end date of each fiscal week in our fiscal year. Its useful but its only for 2015 and moreover I could do with the information in digital form rather than dead tree form. I figured I could achieve the same using Excel, the resultant workbook can be seen at: http://1drv.ms/1FgIXSb (Annoyingly, I have configured the pivot table to display the values as dates not numbers, but it seems Excel Online doesn't honour that, so you will have to download the document and simply refresh the pivot table in order to view it properly.)

SNAGHTML5bda86

It allows you to pick the month in which the fiscal year starts (ours is March so that’s what is currently set in the workbook):

SNAGHTML59027f


and assumes that the first Monday of that month is the first day of the fiscal year.

Doubtless there's easier ways to do this (there's probably a function in Excel that I don't know about) but nevertheless, this works for me and it was kinda fun figuring it out – there’s lots of formulae involved here if you care to dig in to it. Thought I'd share it in case it was useful for anyone else. (Probably best not trust that its correct either, you might want to check it).

@Jamiet

Published Wednesday, September 16, 2015 10:02 PM by jamiet
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement