Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk equivilent of Excel WEEKNUM()

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk equivilent of Excel WEEKNUM()
# 1  
Old 07-11-2014
awk equivilent of Excel WEEKNUM()
I am trying to calculate the week (Sun-Sat) number for a given date with week 01 defined as the week that ends in the new year (the week with the first Saturday of the new year).

Excel WEEKNUM() calls the week containing Jan 1 the first week. Using this, I can get what I want above by calculating the week of the next Saturday (or current date if it is Saturday) using
Code:
=WEEKNUM(A1+7-WEEKDAY(A1))

Where WEEKDAY() returns 1-7 for Sun-Sat.

Fortunately, AWK strftime() with the %w format returns 0-6 for Sun-Sat so I can just add one to get the Excel WEEKDAY() equivilent. I know I have do work in seconds in AWK not days in Excel, just keeping it high level concept for now.

Week number is more tricky. The %g and %G format return the ISO 8601 week number which is defines week 1 as the week with the year's first Thursday. %U returns a week number with week 1 defined as the week with the first Sunday of the new year. %V uses ISO 8601 but weeks run Monday-Sunday. Finally %W considers the first Monday of the new year to define week 0 (does not start at 1).

I need to get straight to my definition (1st Saturday of the year) or indirectly as I do in Excel, however every initial week definition in strftime() is based on a week day (first Thursday, Sunday, or Monday) not a day of the month (Jan 1).

Mike

Last edited by Michael Stora; 07-11-2014 at 02:06 PM..
# 2  
Old 07-11-2014
What if the week doesn't end on a new year? It's a Thursday this year for example.

You can do mktime("2014 12 31 00 00 00"); to get the last day of the year in seconds, and calculate from there.
# 3  
Old 07-11-2014
Quote:
Originally Posted by Corona688
What if the week doesn't end on a new year? It's a Thursday this year for example.
Under the way I am trying to do it Dec 29, 2013 through Jan 4, 2014 is week 1 of 2014 and Dec 28, 2014 through Jan 3, 2014 is week 1 of 2015.

Excel happens to cut the weeks the same way for 2103/2014/2015 transitions this year (but not all years!), but refuses to change year resulting in partial weeks with 53(and sometimes 54) being the same week as 1:
Code:
Date               =WEEKNUM(Date)     =WEEKNUM(Date+7-WEEKDAY(A1))
12/29/2013              53                                  1
1/4/2014                1                                   1
12/28/2014              53                                  1
1/3/2015                1                                   1

Quote:
You can do mktime("2014 12 31 00 00 00"); to get the last day of the year in seconds, and calculate from there.
It is significantly more complicated than that. However, I have contemplated making an array table of the beginning dates of the first week of the year as a kludge solution.
Code:
start[2014]=mktime(2013 12 29); start[2015]=mktime("2015 12 28") and so on . . .

Mike
Last edited by Michael Stora; 07-11-2014 at 03:29 PM..
# 4  
Old 07-11-2014
So you want to fast-forward to 'friday' so if the year changes, it becomes the first week?
# 5  
Old 07-11-2014
Quote:
Originally Posted by Corona688
So you want to fast-forward to 'friday' so if the year changes, it becomes the first week?
Saturday actually. However there will be years in which my method and the AWK methods cut the years differently. It won't happen in all years, but some of them.

Mike
# 6  
Old 07-11-2014
And what do you want to do for those?
# 7  
Old 07-11-2014
Quote:
Originally Posted by Corona688
And what do you want to do for those?
Treat them as I defined in my first post.

I can always translate between Excel's method and mine because the first Saturday of a year is always either the 1st or after the 1st. Since none of the AWK available definitions of week number are day of the month based, that does not work.

Now if I were to advance to the next Saturday (which I know how to do in AWK--basically add 6 days of seconds and subtract one day of seconds times the weekday number from the %w format ) and test the week number with either %g or %G, it would work for most years but break for years in which the 1st falls on a Friday or Saturday. I could test for that and handle the exception but even that exception is not always easy as the correct week might be one more than the return value or 51 or 52 (and possibly even 53--some methods can split 54 week years, I don't know without looking if ISO 8601 can ) less than the return value.

Mike

---------- Post updated at 04:34 PM ---------- Previous update was at 11:59 AM ----------

Anyway, here is my working kludge which will break in a decade . . .

The parts in BOLD deal with the week number calculation.
Code:
    awk -F, 'BEGIN { getline < "'"$indexFile"'" #Discard Headers
                     # Build an associative array for left outer join
                     while (getline < "'"$indexFile"'" ) { split($0,locIndex,","); indexArr[locIndex[1]]=locIndex[2] }
                     close("'"$indexFile"'")
                 # These are the start dates (at midnight) for each Work Week "year".  The script will break after 2025.
                 yearStart[2010] = mktime("2009 12 27 00 00 00"); yearStart[2011] = mktime("2010 12 26 00 00 00");
                 yearStart[2012] = mktime("2012 01 01 00 00 00"); yearStart[2013] = mktime("2013 12 29 00 00 00");
                 yearStart[2014] = mktime("2013 12 29 00 00 00"); yearStart[2015] = mktime("2014 12 28 00 00 00");
                 yearStart[2016] = mktime("2015 12 27 00 00 00"); yearStart[2017] = mktime("2017 01 01 00 00 00");
                 yearStart[2018] = mktime("2017 12 31 00 00 00"); yearStart[2019] = mktime("2018 12 30 00 00 00");
                 yearStart[2020] = mktime("2019 12 29 00 00 00"); yearStart[2021] = mktime("2020 12 27 00 00 00");
                 yearStart[2022] = mktime("2021 12 26 00 00 00"); yearStart[2023] = mktime("2023 01 01 00 00 00");
                 yearStart[2024] = mktime("2023 12 31 00 00 00"); yearStart[2025] = mktime("2024 12 29 00 00 00");
                 yearStart[2026] = mktime("2025 12 28 00 00 00")
                 yearStart[2027] = mktime("9999 12 31 00 00 00") # to prevent array overflow in loop below
                    }
             NR==1 { print "headers" } # add the correct headers later
             NR>=2 { split($7,a," "); date = a[1]; time = a[2];
                     split(date,d,"/"); month = sprintf("%02d",d[1]); day = sprintf("%02d",d[2]); year = 2000 + d[3] % 100
                 dateStamp = mktime(year" "month" "day" 00 00 00")
                     $19 == "Y"? valid = "0" : valid = "N"
                     locID=indexArr[$6"-"$21]
                 for (i = 2010; dateStamp > yearStart[i]; i++); YYYY = i-1 # Last year that passed condition
                 WW = int(( dateStamp - yearStart[YYYY] ) / (7*24*60*60)) + 1 # now that we know the "year" how many weeks are we into it?
                 (YYYY == 2009| YYYY == 2026)? YYYYWW = "ERROR" : YYYYWW = YYYY * 100 + WW
                     print date","time","$23","$9","$10","$11","$12","YYYYWW","valid","locID","$6","$22}'

I'd love to find a more elegant solution. This just bothers me on a gut level. I have no data older than 2010 and I won't be doing the same job in >10 years but it is still a kludge.

Mike
Last edited by Michael Stora; 07-11-2014 at 09:33 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Excel vlookup function like value mapping with awk

I have two files File1 175552 st_497858.1 rs86052.1 rs92185.1 st_001022416.1 174841 175552_174841 179912 st_001122967.2 rs90435.1 rs89122.1 st_001022583.1 175545 179912_175545 179912 st_001122967.2 rs90435.1 rs89122.1 st_001022584.1 175545 179912_175545 179967 st_001256606.1 rs93516.2... (1 Reply)
Discussion started by: sammy777888
1 Replies

2. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

3. UNIX for Advanced & Expert Users

awk - If then else rule like Excel cell

Hi, I need extract / transpose where "Y" in the input file My input file is Item,EB,SB,SD,TP,GR LP,Y,N,N,N,Y GC,Y,N,N,N,N CO,N,Y,Y,Y,Y PS,Y,N,N,Y,Y Expecting output is EB-/'LP/',/'GC/',/'PS/' SB-/'CO/' SD-/'CO/' TP-/'CO/',/'PS/' GR-LP,CO,PS I appreciate for your help Regards (4 Replies)
Discussion started by: myrole
4 Replies

4. Shell Programming and Scripting

Perl script to Merge contents of 2 different excel files in a single excel file

All, I have an excel sheet Excel1.xls that has some entries. I have one more excel sheet Excel2.xls that has entries only in those cells which are blank in Excel1.xls These may be in different workbooks. They are totally independent made by 2 different users. I have placed them in a... (1 Reply)
Discussion started by: Anamika08
1 Replies

5. Shell Programming and Scripting

Help with selecting column with awk for a txt file generated by excel

I am new to scripting/programming, so I apologize for any novice questions. I have a tab delimited text file that was saved from excel xls file. I am trying to select only the third column using awk command. My command line is as below: cat test.txt | awk '{print $3}' However, above... (8 Replies)
Discussion started by: SangLad
8 Replies

6. UNIX for Dummies Questions & Answers

AWK to excel

I've looked at a few different posts and none have entirely answered my question. Wondering how i'd go about using AWK to export to an excel file, which i gather would be a .csv file from what I've read. Atm my AWK command will read 2 files and export 2 files. Each of those files have 2... (3 Replies)
Discussion started by: Aussiemick
3 Replies

7. Shell Programming and Scripting

Comparison of Cells in EXCEL using awk

Hi I have 2 csv files which looks like the following and i have to compare the 2 CSVs and create a third file such that if the value of the 1st cell in A.CSV and the value of the first cell in the B.CSV are same, it should print "SAME" in the third file or else print NOT SAME. Likewise i need... (19 Replies)
Discussion started by: meva
19 Replies

8. Shell Programming and Scripting

printing two lines in awk as two columns in excel

hi guys, i would like to print two lines from a file as two adjacent columns using excel using awk.. i have this so far: awk '{for(i=1; i<=NF; i++) {printf("%s\n",$i)}}' "$count".ttt > "$count".csv #this to print the first line from the .ttt file as rows of the first column in the .csv... (9 Replies)
Discussion started by: npatwardhan
9 Replies

9. Shell Programming and Scripting

PERL: Split Excel Workbook to Indiv Excel files

Hi, I am trying to find a way to read an excel work book with multiple worksheets. And write each worksheet into a new excel file using perl. My environment is Unix. For example: I have an excel workbook TEST.xls and it has Sheet1, Sheet2, Sheet3 worksheets. I would like to create... (2 Replies)
Discussion started by: sandeep78
2 Replies

10. Shell Programming and Scripting

use awk to read randomly located columns in an excel file

Hi, I have an excel file that have a random count of columns/fields and what im trying to do is to only retrieve all the rows under 2 specific field headers. I can use the usually command for awk which is awk 'print{ $1 $2}' > output.txt, but the location of the 2 specific field headers is... (9 Replies)
Discussion started by: mdap
9 Replies
Login or Register to Ask a Question