Sponsored Content
Top Forums Shell Programming and Scripting awk equivilent of Excel WEEKNUM() Post 302909105 by Michael Stora on Monday 14th of July 2014 12:05:56 PM
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
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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
CALENDAR_CONF(5)					   Sun Grid Engine File Formats 					  CALENDAR_CONF(5)

NAME
calendar_conf - Sun Grid Engine calendar configuration file format DESCRIPTION
calendar_conf reflects the format of the Sun Grid Engine calendar configuration. The definition of calendars is used to specify "on duty" and "off duty" time periods for Sun Grid Engine queues on a time of day, day of week or day of year basis. Various calendars can be imple- mented and the appropriate calendar definition for a certain class of jobs can be attached to a queue. calendar_conf entries can be added, modified and displayed with the -Acal, -acal, -Mcal, -mcal, -scal and -scall options to qconf(1) or with the calendar configuration dialog of the graphical user interface qmon(1). Note, Sun Grid Engine allows backslashes () be used to escape newline ( ewline) characters. The backslash and the newline are replaced with a space (" ") character before any interpretation. FORMAT
calendar_name The name of the calendar to be used when attaching it to queues or when administering the calendar definition. See calendar_name in sge_types(1) for a precise definition of valid calendar names. year The queue status definition on a day of the year basis. This field generally will specify on which days of a year (and optionally at which times on those days) a queue, to which the calendar is attached, will change to a certain state. The syntax of the year field is defined as follows: year:= { NONE | year_day_range_list=daytime_range_list[=state] | year_day_range_list=[daytime_range_list=]state | state} Where o NONE means, no definition is made on the year basis o if a definition is made on the year basis, at least one of year_day_range_list, daytime_range_list and state always have to be present, o all day long is assumed if daytime_range_list is omitted, o switching the queue to "off" (i.e. disabling it) is assumed if state is omitted, o the queue is assumed to be enabled for days neither referenced implicitly (by omitting the year_day_range_list) nor explicitly and the syntactical components are defined as follows: year_day_range_list := {yearday-yearday|yearday},... daytime_range_list := hour[:minute][:second]- hour[:minute][:second],... state := {on|off|suspended} year_day := month_day.month.year month_day := {1|2|...|31} month := {jan|feb|...|dec|1|2|...|12} year := {1970|1971|...|2037} week The queue status definition on a day of the week basis. This field generally will specify on which days of a week (and optionally at which times on those days) a queue, to which the calendar is attached, will change to a certain state. The syntax of the week field is defined as follows: week:= { NONE | week_day_range_list[=daytime_range_list][=state] | [week_day_range_list=]daytime_range_list[=state] | [week_day_range_list=][daytime_range_list=]state} ... Where o NONE means, no definition is made on the week basis o if a definition is made on the week basis, at least one of week_day_range_list, daytime_range_list and state always have to be present, o every day in the week is assumed if week_day_range_list is omitted, o syntax and semantics of daytime_range_list and state are identical to the definition given for the year field above, o the queue is assumed to be enabled for days neither referenced implicitly (by omitting the week_day_range_list) nor explicitly and where week_day_range_list is defined as week_day_range_list := {weekday-weekday|weekday},... week_day := {mon|tue|wed|thu|fri|sat|sun} with week_day ranges the week_day identifiers must be different. SEMANTICS
Successive entries to the year and week fields (separated by blanks) are combined in compliance with the following rule: o "off"-areas are overridden by overlapping "on"- and "suspended"-areas and "suspended"-areas are overridden by "on"-areas. Hence an entry of the form week 12-18 tue=13-17=on means that queues referencing the corresponding calendar are disabled the entire week from 12.00-18.00 with the exception of Tuesday between 13.00-17.00 where the queues are available. o Area overriding occurs only within a year/week basis. If a year entry exists for a day then only the year calendar is taken into account and no area overriding is done with a possibly conflicting week area. o the second time specification in a daytime_range_list may be before the first one and treated as expected. Thus an entry of the form year 12.03.2004=12-11=off causes the queue(s) be disabled 12.03.2004 from 00:00:00 - 10:59:59 and 12:00:00 - 23:59:59. EXAMPLES
(The following examples are contained in the directory $SGE_ROOT/util/resources/calendars). o Night, weekend and public holiday calendar: On public holidays "night" queues are explicitly enabled. On working days queues are disabled between 6.00 and 20.00. Saturday and Sun- day are implicitly handled as enabled times: calendar_name night year 1.1.1999,6.1.1999,28.3.1999,30.3.1999- 31.3.1999,18.5.1999-19.5.1999,3.10.1999,25.12.1999,26 .12.1999=on week mon-fri=6-20 o Day calendar: On public holidays "day"-queues are disabled. On working days such queues are closed during the night between 20.00 and 6.00, i.e. the queues are also closed on Monday from 0.00 to 6.00 and on Friday from 20.00 to 24.00. On Saturday and Sunday the queues are disabled. calendar_name day year 1.1.1999,6.1.1999,28.3.1999,30.3.1999- 31.3.1999,18.5.1999-19.5.1999,3.10.1999,25.12.1999,26 .12.1999 week mon-fri=20-6 sat-sun o Night, weekend and public holiday calendar with suspension: Essentially the same scenario as the first example but queues are suspended instead of switching them "off". calendar_name night_s year 1.1.1999,6.1.1999,28.3.1999,30.3.1999- 31.3.1999,18.5.1999-19.5.1999,3.10.1999,25.12.1999,26 .12.1999=on week mon-fri=6-20=suspended o Day calendar with suspension: Essentially the same scenario as the second example but queues are suspended instead of switching them "off". calendar_name day_s year 1.1.1999,6.1.1999,28.3.1999,30.3.1999- 31.3.1999,18.5.1999-19.5.1999,3.10.1999,25.12.1999,26 .12.1999=suspended week mon-fri=20-6=suspended sat-sun=suspended o Weekend calendar with suspension, ignoring public holidays: Settings are only done on the week basis, no settings on the year basis (keyword "NONE"). calendar_name weekend_s year NONE week sat-sun=suspended SEE ALSO
sge_intro(1), sge__types(1), qconf(1), queue_conf(5). COPYRIGHT
See sge_intro(1) for a full statement of rights and permissions. SGE 6.2u5 $Date$ CALENDAR_CONF(5)
All times are GMT -4. The time now is 08:07 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy