Sponsored Content
Top Forums Shell Programming and Scripting awk equivilent of Excel WEEKNUM() Post 302908907 by Michael Stora on Friday 11th of July 2014 11:35:05 AM
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..
 

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
cronosplit(1m)															    cronosplit(1m)

NAME
cronosplit - split log files into cronolog-compatible files SYNOPSIS
cronosplit --template=TEMPLATE [--print-invalid] [--help] [--version] file ... DESCRIPTION
cronosplit is a simple program that reads lines from a set of input log files, which must be in Common Log Format or NCSA Combined/XLF/ELF Format and write each lines to an output files, the name of which is constructed using the template specified and timestamp from the the line. The template uses the same format specifiers as the Unix date(1) command (which are the same as the standard C strftime library function). Options cronosplit accepts the following options and arguments: --template=TEMPLATE specifies the template for the output log files (using the format specifiers described below). --print-invalid print invalid log file entries to the standard error stream. --utime update modification time of output file according to last parsed log entry. --verbose print additional status messages to the standard error stream. --debug print debug messages to the standard error stream. --help print a help message and then exit. --version print version information and exit. Template format Each character in the template represents a character in the expanded filename, except for date and time format specifiers, which are replaced by their expansion. Format specifiers consist of a `%' followed by one of the following characters: % a literal % character n a new-line character t a horizontal tab character Time fields: H hour (00..23) I hour (01..12) p the locale's AM or PM indicator M minute (00..59) S second (00..61, which allows for leap seconds) X the locale's time representation (e.g.: "15:12:47") Z time zone (e.g. GMT), or nothing if the time zone cannot be determined Date fields: a the locale's abbreviated weekday name (e.g.: Sun..Sat) A the locale's full weekday name (e.g.: Sunday .. Saturday) b the locale's abbreviated month name (e.g.: Jan .. Dec) B the locale's full month name, (e.g.: January .. December) c the locale's date and time (e.g.: "Sun Dec 15 14:12:47 GMT 1996") d day of month (01 .. 31) j day of year (001 .. 366) m month (01 .. 12) U week of the year with Sunday as first day of week (00..53, where week 1 is the week containing the first Sunday of the year) W week of the year with Monday as first day of week (00..53, where week 1 is the week containing the first Monday of the year) w day of week (0 .. 6, where 0 corresponds to Sunday) x locale's date representation (e.g. today in Britain: "12/04/96") y year without the century (00 .. 99) Y year with the century (1970 .. 2038) Other specifiers may be available depending on the C library's implementation of the strftime function. SEE ALSO
apache(1m) cronolog(1m) date(1) strftime(3) environ(5) More information and the latest version of cronolog and cronosplit can be obtained from http://www.ford-mason.co.uk/resources/cronolog/ If you have any suggestions, bug reports, fixes, or enhancements, please mail them to the author. More about Apache Documentation for the Apache http server is available from http://www.apache.org AUTHOR
Andrew Ford <A.Ford@ford-mason.co.uk> cronosplit is based on a script called splitlog by Roy Fielding, which is part of the wwwstat package. March 1998 cronosplit(1m)
All times are GMT -4. The time now is 04:54 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy