2

I am creating a clock-in time system and so far I have been able to get user clock in time for today and user clock in time for the current week.

The final step is to get user current time for the current pay period.

I have created a list of pay period start & end dates in Excel.

Whenever you use a function like Excel WEEKNUM() or MySQL YEARWEEK(), these functions come with an additional option parameter.

The links below show the differences between these modes in a table.

Excel WEEKNUM() table reference

MySQL YEARWEEK() table reference

My question is, if we do payroll biweekly, which mode do I set in Excel WEEKNUM() that corresponds to MySQL YEARWEEK()?

Attached spreadsheet clock.logic.xlsx

Thank you for any help.

suchislife
  • 4,251
  • 10
  • 47
  • 78
  • 2
    This site is getting to the point where posting questions just earns you bad reputation. Thanks for the down vote. It helps a lot. It's not like I've researched this question before posting or anything. Thanks again. – suchislife Jan 14 '18 at 06:06
  • 2
    Some "genius" see similar keywords come up so it must be a duplicate. That is all the research it takes to click down vote. – suchislife Jan 14 '18 at 06:09
  • You are right about the down-votes. But the corresponding functions are [WEEKNUM function](https://support.office.com/en-us/article/weeknum-function-e5c43a03-b4ab-426c-b411-b18c13c75340) or [ISOWEEKNUM function](https://support.office.com/en-us/article/isoweeknum-function-1c2d0afe-d25b-4ab1-8894-8d0520e90e0e) in `Excel` and [WEEK](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week) in `MySQL`. Please describe what issues you have while trying getting the same results from both. – Axel Richter Jan 14 '18 at 06:17
  • Which mode for WEEK in MySQL for biweekly pay periods? 1, 5 or 7? – suchislife Jan 14 '18 at 06:51
  • This is precisely what I was looking for. Not sure how to accept your comment as an answer. – suchislife Jan 14 '18 at 07:06

2 Answers2

2

At first the good news: The Excel ISOWEEKNUM function corresponds to the MySQL WEEKOFYEAR which is WEEK(date,3). So determining ISO week numbers is possible.

But all other WEEK modes are simply crap because the definition of the first week in year does not fit any logic used elsewhere. For example, take the simplest mode having Sunday as the first day of the week and the first week of the year is the week, the first day of the year falls in. This is what Excels WEEKNUM function returns with Return_type 1 or omitted. This should be MySQLs WEEK in modus 0 (0-53) or 2 (1-53). But what the heck?

SELECT WEEK('2008-01-01',0); -> 0

SELECT WEEK('2008-01-01',2); -> 52

So MySQL tells us, Tuesday, 2008-01-01, is in week 52 of 2007?

Really? Why?

Because the rule "Week 1 is the first week … with a Sunday in this year" is not fulfilled by MySQL. Instead it seems for MySQL the first week starts with the first Sunday in this year.

So except of the ISO week numbers, all other week numbers from MySQL are wrong. One could think: Let us take modus 0 and simply add 1 to the result. But that fails in 2012. Because there 2012-01-01 is Sunday and there MySQL gives week number 1 in modus 0 as well as in modus 2.

Examples:

Excel:

Date        WEEKNUM ISOWEEKNUM
2008-01-01  1       1
2008-02-01  5       5
2008-02-03  6       5
2008-02-04  6       6
2008-12-31  53      1
2009-01-01  1       1
2009-02-01  6       5
2009-12-31  53      53
2012-01-01  1       52
2012-02-01  5       5
2012-12-31  53      1
2016-01-01  1       53
2016-02-01  6       5
2016-12-31  53      52

MySQL:

drop table if exists tmp;

create table tmp (d date);

insert into tmp (d) values 
('2008-01-01'),
('2008-02-01'),
('2008-02-03'),
('2008-02-04'),
('2008-12-31'),
('2009-01-01'),
('2009-02-01'),
('2009-12-31'),
('2012-01-01'),
('2012-02-01'),
('2012-12-31'),
('2016-01-01'),
('2016-02-01'),
('2016-12-31');

select d as 'Date', week(d,0), week(d,3) from tmp;

Result:

Date        week(d,0) week(d,3)     
2008-01-01  0         1
2008-02-01  4         5
2008-02-03  5         5
2008-02-04  5         6
2008-12-31  52        1
2009-01-01  0         1
2009-02-01  5         5
2009-12-31  52        53
2012-01-01  1         52
2012-02-01  5         5
2012-12-31  53        1
2016-01-01  0         53
2016-02-01  5         5
2016-12-31  52        52
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • 1
    I have updated the post with the excel spreadsheet I'm using for your review. Please let me know if you would like to add any final thoughts based on how I've created the spreadsheet. – suchislife Jan 14 '18 at 15:28
  • Not clear what you are trying to achieve with that excel spreadsheet and how this is related to `MySQL`. But glad to see you are using `ISOWEEKNUM` as this is a proper defined standard. But are you sure this will be tolerated in a country which measures lengths in inches, foots and yards instead of meters and weights in ounces and pounds instead of kilograms and where dates are in format M/D/Y instead of YYYY-MM-DD? – Axel Richter Jan 14 '18 at 15:56
  • The excel sheet is my humble first attempt at finally creating a SELECT query that with results ranging from Pay-Period-Start-Date to Pay-Period-End-Date given CURRDATE(). – suchislife Jan 14 '18 at 16:37
  • But always not clear to me. I suspect what you really needs is calculation the date of the first Monday in the year. Or in other words the date of Monday in the first ISO week of the year. This will be `=DATE(B4,1,7)+(2-WEEKDAY(DATE(B4,1,7)))-7*(ISOWEEKNUM(DATE(B4,1,1))=1)` where `B4` contains the year. See also https://stackoverflow.com/questions/45392608/given-an-iso-8601-week-number-get-date-of-first-day-of-that-week-in-libreoffice/45397292#45397292. – Axel Richter Jan 14 '18 at 17:24
  • @AxelRichter - simpler formula to calculate date of 1st Monday in ISO year `=DATE(B4,1,5)-WEEKDAY(DATE(B4,1,3))` – barry houdini Jan 15 '18 at 23:13
0

If you want to calculate hours in current pay period in Excel, given a two week pay period, then I'd suggest that you don't need week numbers at all (in fact that overcomplicates the calculation, especially at the start or end of the year)

If you have dates in A2:A100 and hours worked on those dates in B2:B100, and a list of pay period start dates in Z2:Z10 then you can get hours in current pay period with this formula

=SUMIF(A2:A100,">="&LOOKUP(TODAY(),Z2:Z10),B2:B100)

I imagine your actual setup is more complicated, but some variation on the above can probably still be used

barry houdini
  • 45,615
  • 8
  • 63
  • 81