Convert Excel formula to Perl??

[kre1973]

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

Excel code below:

=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

 

[MikeLacey]

Hi,

What's the error you're seeing with Spreadsheet::WriteExcel? (before you go and write something yourself

Mike

When working on any project the value of other people is exactly that - they are other people, with views that don't necessarily match yours. This mismatch, between their views and the view you've been contentedly assuming is right, is where that value lies.