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()
# 8  
Old 07-11-2014
If you really want to go to a lot of trouble, run localedef -

read the localdef man file for your system, get a series of localedef inputfiles (on Linux usually /usr/share/I18n...). Piddle around with the time and calendar file. Use YOUR locale files, change a few things, compile the files with localedef, use a unique output name for your locale. Or break your system. Put the compiled file wherever it needs to go (localedef man page). Run your code pretty much without all the gobbledygook about week start. Instead set your locale to the modified one as the first line of a shell script - then run awk code. Underlying the awk interpreter is C code which speaks locale.

I've had to do this for some calendrics I did a long time ago. It is highly OS specific.
Since it appears you are doing this on a Linux box (GNU gawk probably! Because POSIX awk does not do date/time C calls like mktime), I am not in a position to make more explicit suggestions.
Last edited by jim mcnamara; 07-11-2014 at 10:17 PM..
# 9  
Old 07-12-2014
The elegant solution came to me when I was pumping gas on my way home today.

I'm not going to work on it this weekend but it is simple in hindsight.
Calculate the week day (0-6), add the right number of seconds to get to Saturday (6-weekday)*24*60*60. Determine the year of that Saturday. Then determine the week day of Jan 1 of that calculated year and subtract enough seconds to get to Sunday -weekday*24*60*60. That gives you your reference of the beginning of that "year". Subtract that from the time in question, divide by 7 days (7*25*60*60) drop the fractions and add 1 to get the "week" of that "year" the date corresponds to.

Because there is a reliable relationship between the weeks under my definition and in Excel, I was looking for a similar transformation from one of the AWK week number definitions. Instead, the easy answer relied on manipulating the weekdays and not the week numbers.

Mike
# 10  
Old 07-13-2014
This would be very easy from the shell with a little help from my datecalc script.

Code:
$ cat weeknum
#! /usr/bin/ksh

alias datecalc=./datecalc
year=$1
month=$2
day=$3
mjd=$(datecalc -j $year $month $day)

day1=$(datecalc -d $year 1 1)
mjd1=$(datecalc -j $year 1 1)
((mjd1s=mjd1+(6-(day1%7)) ))
#echo first saturday is $(datecalc -j $mjd1s)
((weekno=(mjd-mjd1s+7)/7))
echo weekno = $weekno

exit 0

$ ./weeknum 2014 7 13
weekno = 28
$

# 11  
Old 07-13-2014
Quote:
Originally Posted by Michael Stora
The elegant solution came to me when I was pumping gas on my way home today.

I'm not going to work on it this weekend but it is simple in hindsight.
Calculate the week day (0-6), add the right number of seconds to get to Saturday (6-weekday)*24*60*60. Determine the year of that Saturday. Then determine the week day of Jan 1 of that calculated year and subtract enough seconds to get to Sunday -weekday*24*60*60. That gives you your reference of the beginning of that "year". Subtract that from the time in question, divide by 7 days (7*25*60*60) drop the fractions and add 1 to get the "week" of that "year" the date corresponds to.

Because there is a reliable relationship between the weeks under my definition and in Excel, I was looking for a similar transformation from one of the AWK week number definitions. Instead, the easy answer relied on manipulating the weekdays and not the week numbers.

Mike
Your algorithm seems to agree with excel:

Code:
awk -v y=$1 -v m=$2 -v d=$3 '
BEGIN {
   to=mktime(y " " m " " d " 0 0 0");
   to+=24*60*60*(6 - strftime("%w", to))
   first=mktime(strftime("%Y", to) " 1 1 0 0 0");
   first-= 24*60*60*(strftime("%w",first))
   print strftime("  First: %a %d %m %Y", first)
   print strftime("     To: %a %d %m %Y", to)
   print "weeknum: " int((to-first)/(7*24*60*60))+1
}'

Except for dates close to the end of the year which appear as 1 instead of 51/2/3
# 12  
Old 07-14-2014
Quote:
Originally Posted by Chubler_XL
Your algorithm seems to agree with excel:

Code:
awk -v y=$1 -v m=$2 -v d=$3 '
BEGIN {
   to=mktime(y " " m " " d " 0 0 0");
   to+=24*60*60*(6 - strftime("%w", to))
   first=mktime(strftime("%Y", to) " 1 1 0 0 0");
   first-= 24*60*60*(strftime("%w",first))
   print strftime("  First: %a %d %m %Y", first)
   print strftime("     To: %a %d %m %Y", to)
   print "weeknum: " int((to-first)/(7*24*60*60))+1
}'

Except for dates close to the end of the year which appear as 1 instead of 51/2/3
In Excel I drag those dates kicking and screaming into the new year with
Code:
=WEEKNUM(A2+7-WEEKDAY(A2))

Calender issues can be really interesting. Unlike ISO (defined by Thursday) weeks, I can always count on Excel not being offset by a week even if it refuses to turn over the number to 1 before new years day. In doing more research it appears my employer seems to have devised their calendar (long before the ISO standard) so that we have the exact same number of work holidays every "year" (9) rather than 8 or 10 in some years.

Mike
# 13  
Old 07-15-2014
Code:
for i in $(echo {1..7}); do cal $i 2014; done | awk '!/[A-Z]/&&NF>0{print} END{print "\n"}' | awk 'NR==1{print}NR>2{if(NF<7){a=$0;getline;print a,$0;a="";}else{print}}' | awk -v day=13 '$0~day{output=NR}END{print output}'

# 14  
Old 07-15-2014
I get 28 when I run that. I could see getting the same info from cal as strftime with %w but why???

Mike
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