Sponsored Content
Top Forums Shell Programming and Scripting awk equivilent of Excel WEEKNUM() Post 302908923 by Michael Stora on Friday 11th of July 2014 02:21:45 PM
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..
 

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 01:32 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy