We help IT Professionals succeed at work.

# Convert Excel formula to Perl??

on
1,549 Views
I'm using the Perl module: Spreadsheet::WriteExcel in which I'm trying to create a Excel spreadsheet. It's seems that the formula below is maybe to complex for the modules to interpet.

What the formula is basically trying to say is that, how many time entries in the cell range of E2:E23 are within a time range of 6:30pm and 7:00am.

typically data:

4:15am
3:36am
1:19am
5:10am
5:47am
3:49am
3:57am
2:09am
4:24am
4:15am
6:12am
5:49am

=SUM(IF(NOT(ISERROR(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(UPPER(E2:E23),"AM"," AM"),"PM", " PM")))),IF((TIMEVALUE(SUBSTITUTE(SUBSTITUTE(UPPER(E2:E23),"AM"," AM"),"PM", " PM"))<TIME(7,0,0))+(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(UPPER(E2:E23),"AM"," AM"),"PM", " PM"))>TIME(18,30,0))>0,1,0),0))

The answer in this case would be 12. All the data in the range are within the time frame.

So, that's what I'm looking to do in Perl somehow, maybe with a counter that keeps track of the entries that are within the time frame.

thanks
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2015

Commented:
Is that starting at 6:30pm and ending at 7:00am, or  starting at 7:00am and ending at 6:30pm?
Computer Operations Analyst

Commented:
Starting at 6:30pm and ending at 7:00am
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2015

Commented:
\$\=\$/;
print scalar grep{
my (\$h,\$m,\$p)=/(\d+):(\d+)(p?)/;
\$h = (\$h%12)+(\$p&&12);
\$m && ((\$h*60+\$m) - ((6+12)*60+30)) % (24*60+0) <= ((7*60+0) - ((6+12)*60+30)) % (24*60+0)

} <DATA>
__DATA__
4:15am
3:36am
1:19am
5:10am
5:47am
3:49am
3:57am
2:09am
4:24am
4:15am
6:12am
Computer Operations Analyst

Commented:
ozo, your solution code does work, but I'm not sure how I would incorporate it in my code.

Anyway here's all my code:

#!/opt/perl/bin/perl

my \$InputFile = "/home/kre1973/Output.txt";
my \$OutputFile = "//ho/dfs01/work/IS/Operations/GROUP0K/Automation/EBIS test.xls";

open (INPUT, "\$InputFile") or die "\$InputFile \$!";

\$format->set_text_wrap();
\$format->set_center_across();
\$format->set_properties(bold => 1, border => '1', size => '8');

\$worksheet->write('E1', 'TNC Client Information End Time (SLA 7am)', \$format);
\$worksheet->write('G1', 'Risk Product Information End Time (SLA 7am)', \$format);
\$worksheet->write('I1', 'TNC/History Mart CTMHM End Time (SLA 7am)', \$format);

my \$row = 1;
while (<INPUT>)
{
chomp(\$_);
my (\$TNC, \$RISK, \$CTMHM) = split(/\|/, \$_);

\$worksheet->write(\$row, 4, \$TNC, \$format);
\$worksheet->write(\$row, 6, \$RISK, \$format);
\$\$worksheet->write(\$row, 8, \$CTMHM, \$format);

\$worksheet->write(\$row, 2, map{/ (.*)/,"\n"} (sort
map{my(\$h,\$m)=/(\d+):\d+(\w+)/;
sprintf"%s%d %s",\$m,\$h%12,\$_}\$TNC, \$RISK, \$CTMHM)[-1] ,\$format);
\$row++;
}

close INPUT;

CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
Computer Operations Analyst

Commented:
I'm getting the following error:

Global symbol "\$count" requires explicit package name at ./dailyops line 144.
syntax error at ./
So, I added a "my \$count" like this:

map{my(\$h,\$m,\$p)=/(\d+):(\d+)(p?)/;
\$h = (\$h%12)+(\$p&&12);
my \$count += ((\$h*60+\$m) - ((6+12)*60+30)) % (24*60+0) <= ((7*60+0) - ((6+12)*60+30)) % (24*60+0);
sprintf"%02d %s",\$h,\$_}

Now, I'm getting this error:
syntax error at ./dailyops line 145, near "},"

These are the lines from 142 - 145:

142 \$worksheet->write('E27', map{my(\$h,\$m,\$p)=/(\d+):(\d+)(p?)/;
143       \$h = (\$h%12)+(\$p&&12);
144       my \$count += ((\$h*60+\$m) - ((6+12)*60+30)) % (24*60+0) <= ((7*60+0) - ((6+12)*60+30)) % (24*60+0);
145      sprintf"%02d %s",\$h,\$_}, \$format);
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2015

Commented:
#!/opt/perl/bin/perl

my \$InputFile = "/home/kre1973/Output.txt";
my \$OutputFile = "//ho/dfs01/work/IS/Operations/GROUP0K/Automation/EBIS test.xls";
my \$count;

open (INPUT, "\$InputFile") or die "\$InputFile \$!";

\$format->set_text_wrap();
\$format->set_center_across();
\$format->set_properties(bold => 1, border => '1', size => '8');

\$worksheet->write('E1', 'TNC Client Information End Time (SLA 7am)', \$format);
\$worksheet->write('G1', 'Risk Product Information End Time (SLA 7am)', \$format);
\$worksheet->write('I1', 'TNC/History Mart CTMHM End Time (SLA 7am)', \$format);

my \$row = 1;
while (<INPUT>)
{
chomp(\$_);
my (\$TNC, \$RISK, \$CTMHM) = split(/\|/, \$_);

\$worksheet->write(\$row, 4, \$TNC, \$format);
\$worksheet->write(\$row, 6, \$RISK, \$format);
\$\$worksheet->write(\$row, 8, \$CTMHM, \$format);

\$worksheet->write(\$row, 2, map{/ (.*)/,"\n"} (sort
map{my(\$h,\$m,\$p)=/(\d+):(\d+)(p?)/;
\$h = (\$h%12)+(\$p&&12);
\$count += ((\$h*60+\$m) - ((6+12)*60+30)) % (24*60+0) <= ((7*60+0) - ((6+12)*60+30)) % (24*60+0);
sprintf"%02d %s",\$h,\$_
}\$TNC, \$RISK, \$CTMHM)[-1] ,\$format);
\$row++;
}

close INPUT;

print \$count,"\n";
Computer Operations Analyst

Commented:
I'm getting the count of:36 when it should be: 12.

CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2015

Commented:
it is looking at all of \$TNC, \$RISK, \$CTMHM  maybe they are all in range
Computer Operations Analyst

Commented:
ozo I'm sorry about the confusion, but actually I'm going need separate counts for each one \$TNC, \$RISK, \$CTMHM...
Computer Operations Analyst

Commented:
thanks  a lot ozo, I figured out how to do it separately for all three.
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.