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