I just found a cool Excel function!

[thewayne]

Well, it's not a cool function, it's an option on a function. Specifically, the Weekday function.

I'm finishing up our taxes. Normally I'd finish them in February or March, but it's been a heck of a few months. One of the things that I do is dump all my prescription drug purchases into a spreadsheet and calculate the day of the week, so I can take a mileage deduction on my state taxes for weekend pickups since I'm not working those days.

Nevermind whether or not we're going to dinner or a movie....

Anyway, the function ends up being:

=IF(WEEKDAY(A1,2)>5,42,"")

A1 is my date field of when the transaction takes place. By default, i.e. without a number changing the day of the week for the date the starting DOW is Sunday = 1. By supplying the 2, you're telling Excel that Monday = 1, therefore if the DOW is greater than 5, it's Saturday or Sunday, therefore the weekend! If that's true, plug in 42 (round trip to Alamogordo and back), otherwise make it a blank cell.

Five trips for an additional 210 miles, at $0.21 per mile towards my state taxes! I have to manually eliminate dupes for multiple transactions on the same day, being multiple drugs refilled and picked up at the same time.

I use spreadsheets a fair amount, but not for anything particularly complex, just as a general purpose tool, so I was kinda chuffed to find this. The question is whether or not I'll remember it for next year!

Comments

[garote]

Set an alarm with a one-year delay, to remind you

[warriorsavant]

Gosh, a post about Excel. Will populate my wet dreams for the next week.

[thewayne]

Gonna have to be careful what I post around you!

[warriorsavant]

Same, bro, same.

[disneydream06]

You know me, I wish that I knew computer speak. LOL.....
Hugs, Jon

[thewayne]

I've been doing this stuff for over 40 years, Jon. You know about as much about computers as I know about nursing! :-)

[disneydream06]

LOL!!!!!!!!!!!!!!!!!!!!

[darkoshi]

You can create a "template" spreadsheet with the headers and formulas, and keep it in whatever folder you'll be using for your taxes next year (create the folder now already so you'll find it then).
That's how I do it.

One of my spreadsheets has a column that displays the name of the day of week of the date in the preceding column. It uses a formula like =TEXT(A3,"ddd"). If I want to check for rows corresponding to certain days, I can compare that cell to "Sat", "Sun", etc.
But that WEEKDAY function is a good one to know, and a more straight-forward way of doing it.

[thewayne]

I've used Weekday quite often in my jobs as a database administrator/developer, it's a very useful function when it comes to reporting and queries. Sometimes when you don't have a function that returns the day name but you have a DOW function, you can use that to index you into a lookup table to get the day name.

One thing that I had to do that was mildly interesting was dealing with reports where the fiscal year didn't correspond with the calendar year. For the university, the FY starts 7/1. So if the month is > 6, FY = Year + 1. When the month is <= 6, you're in the next calendar year, and the year = the FY.

The truly weird thing was that if you did FY = 1 + Year (or maybe it was the other way around, I don't remember), the formula didn't work! It was really weird. But reversing the variable and the constant took care of the problem, so I was okay with the solution.

[darkoshi]

Our work uses a fiscal year too, and it is often ambiguous when I hear that something will be "at the end of the year" as to which one they are talking about.

[thewayne]

Yeah, that's always fun.

[moonhare]

Heh, I never warmed up to Excel. The only time I used spreadsheets was at tax time when I would drop Amazon purchases into Google Sheets and figure out what I had to declare for the state. Now I don’t need that as Amazon collects the tax up front…