I complete a timesheet every 14 days. I got tired of doing math in my head, so around August 13, 2010 I put a stop to it. Here’s what the date cell on my timesheet looks like now.
F2 to edit the cell, “+14” and enter. It’s nowhere near too long as formulas go, but it’s starting to bother me. Time to consolidate. Select the 14s.
Press Control+= (F9 works too, but my muscle memory is control and equal sign)
Enter. Next pay period, my timesheet will look like this
Something tells me that there must be a better way. Maybe a formula that uses TODAY and MOD.
I, or rather the payroll person, saves a copy every fourteen days. If I have a formula then I have to paste values. Bleh
Can’t you just write a quick macro and add a button?
How about something like:
=ROUND((TODAY()-DATE(2010,8,13))/14,0)*14+DATE(2010,8,13)
I don’t remember who I posted as around Dec 2011, but was unable to say thanks for your reply, and I do so now. Thank You. I appreciate it very much. I learn a lot from this site (and from the sites of others that post here.)
As far as this post goes, it must work better for you if you’re doing it this way.
One formula in the file is Ok. Some how the payroll person either saves the value or saves the formula.
Can you add any cells or formulas, or is One formula the limit?
Can you take your hard start date plus (14 days times current pay period)? It still leaves you in the same place (having to change your formula), but it shouldn’t make the formula longer.
Can you add a pay period cell? Then you are only incrementing the one cell and the formula could still save and calculate. You are still changing something in the file each time, but I’m guessing you’re changing your work hours anyway.
Are you entering your start/stop times as date and time or just time?
What is the advantage in changing a formula (to change the period end date) or manually entering the date?
Again, I thank you for your reply, website, and knowledge, and I also say Thank You to those that contribute info and ideas on this site.
Dick –
If your timecard is mechanized like mine was (the dates automatically fill in) then why can’t the period end equal what looks to be O6? Or is it that O6 is determined by period end?
… mrt
Use formulas to create a dynamic list of 3 dates. Then use a data validation list to select one of those dates.
On Sheet2, put your starting date in cell C2: =DATE(2010,8,13)
In cell C3, a formula for today: =TODAY()
In C6, calculate the current week end: =C2+INT((C3-C2)/14)*14
In C5, the previous week: =C6-14
In C7, the next week: =C6+14
Name C5:C7 as DateList
On Sheet1, in your date cell, add a data validation drop down for a list, with DateList as the Source.
This is just a simple trick but I bet most Excel user’s don’t know this! Good job on sharing this with others.
Confused: Where would that macro live? It can’t live in the workbook or I might as well use a formula. I’m sure I could code something up that would work, but I can’t off the top of my head imagine it being easier than F2 +14 Enter 26 times per year.
Randy: Good formula. When accounting opens my timesheet from 8/31/10 it’s going to say 4/22/2012, so I can’t use any volatile formulas.
dDan: There are many formulas in this workbook, just no volatile ones. I could use +(14*x) and just increment x. Six of one… This is the pay period cell. No start and stop times, just hour values. The advantage of changing the formula is that I don’t have to think to enter +14, but I do if I just hard code the date. It’s not a ton of thinking, but it doesn’t really add any value. Maybe it would stave off dementia or something.
MRT: Yes, O6 is =$D$3-15+COLUMN()
Debra: That’s genius. I’ve already implemented it.
I don’t get it. If the base date were 2010-08-13, then shouldn’t you be able to use a formula like
=DATE(2010,8,13)+14*44
and you just increment the 44 to 45?
Or if you want to hyperautomate this, add the following to your Personal.xls[|m|b] and give it a keystroke shortcut.
Sub foo()With ActiveCell
If VarType(.Value) = vbDate Then .Value2 = .Value2 + 14
End With
End Sub
Debra: That’s slick! :)
Good one, Deb! That’s why Debra is my favorite female Canadian Excel MVP who wears Crocs.
Debra: I like your solution and with your blessing will use it in one of my worksheets.
While we are talking about timesheets, maybe ya’ll can help. My actual hours are calc’d by punching a time clock, this is for my own use.
My payperiod is 14 days. New payweek starts on Sat. I’m working overnights from 10pm one day to 7am the next morning. One hr for lunch (not hard scheduled), so it might be one hr on one day or one hr the next day.
I want to calc hrs worked each day, each week, and each pay period, zeroing out when situation changes.
So, in a nutshell, working 10pm Fri-7am Sat:
lunch 0200-0300 Sat = 2 hrs Fri, 6 hrs Sat.
lunch 2330 Fri – 0030 Sat = 1.5 hrs Fri, 6.5 hrs Sat.
Sat’s hrs don’t count for last week.
If end of pay period, Sat’s hrs don’t count for last pay period.
Any responses are appreciated.
I was wondering why people don’t do what I do:
Type in the start date, press F9 to convert to a date number, type +7 (or +14), press enter.
Next time is just F2, F9, +7 enter.
Then I remembered that not everyone still has Lotus 123 on their computers :)
But I had no idea that F9 doesn’t convert a date to a date number in Excel!
Interesting… I’m insulated from the problems of date-manipulation in Excel, as all my employers or clients have their own date libraries and calendar files.
A particular annoyance is month-end dates and Adding a month to the end of the month. I thik that your formula will fail if your task is performed at the end of each month, or each quarter: did you ever get a month- or quarter-end date sequence do this to you?
31/08/2005
30/11/2005
28/02/2006
28/05/2006
28/08/2006
28/11/2006
28/02/2007
28/05/2007
28/08/2007
…On the other hand, you might be aware of a useful formula, or of a specific fix to this in the Analysis ToolPak.
Nigel: The trick is to compute the zero-th day f the next month.
So, if the 1st date is in C11, then in C12 enter the formula =DATE(YEAR(C11),MONTH(C11)+4,0). Copy C12 as far down as desired.
Tushar, that’s a great trick with the zeroeth day of the month.
Tushar: that’s almost what I do in my UDF: subtract 1 from the first day of the following month.
‘Zeroeth’ day… That’s a new one on me. New, also, that you could add four to (say) the month of November: I’d always assumed that Excel would raise an invalid date error with ‘Month 15’. But it’s fine: the formula returns month 3 and increments the year. So your formula doesn’t break in October, November and December.
Which in turn means that it can be done in formulae, rather than by UDFs:
‘ Special handling required for adding months at EOM:
‘ VBA.DateAdd(“m”, 1, “28 Feb 2006”) = 28/03/2006 (!)
‘ Business logic is ALWAYS that adding a month to EOM
‘ gives the end of the following month – 31 Mar 2006.
If strLabel = “m” or strLabel = “y” Then
If Month(ReferenceDate) <> Month(ReferenceDate) + 1 Then ‘EOM detected
ReferenceDate = ReferenceDate + 1
AddDate = DateAdd(strLabel, i, ReferenceDate)
AddDate = AddDate – 1
Else
AddDate = DateAdd(strLabel, i, ReferenceDate)
End If
Endif
Doug, Nigel: Thanks. I’ve been using that approach for such a long time that I don’t remember if I first saw it somewhere else or if I came up with it myself.
With Excel 2010 even shorter might be to use the EOMONTH function. I believe it used to be part of the Analysis ToolPak, which means that now it is, of course, part of Excel’s base function set.