We help IT Professionals succeed at work.

Convert Excel formula to Perl??

Richard Kreidl
on
1,549 Views
Last Modified: 2013-11-05
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

ozo
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?
Richard KreidlComputer Operations Analyst

Author

Commented:
Starting at 6:30pm and ending at 7:00am
ozo
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
Richard KreidlComputer Operations Analyst

Author

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

use Spreadsheet::WriteExcel;

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

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

my $workbook = Spreadsheet::WriteExcel->new($OutputFile);
my $worksheet = $workbook->add_worksheet();
 
my $format = $workbook->add_format();
$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)
UNLOCK SOLUTION
Richard KreidlComputer Operations Analyst

Author

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);
ozo
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2015

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

use Spreadsheet::WriteExcel;

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 $!";

my $workbook = Spreadsheet::WriteExcel->new($OutputFile);
my $worksheet = $workbook->add_worksheet();
 
my $format = $workbook->add_format();
$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";
Richard KreidlComputer Operations Analyst

Author

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

ozo
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
Richard KreidlComputer Operations Analyst

Author

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

Author

Commented:
thanks  a lot ozo, I figured out how to do it separately for all three.
Unlock the solution to this question.
Join our community and discover your potential

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.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.