Time (duration) problem

[ale...@gmail.com]

Hi everyone

I'm having some difficulties outputting a time to excel in the correct way. I try to explain myself a little better, I've two MySQL timestamp that i parse using DateTime module, i compute difference to calculate a process duration and than put it in a variable using 

 

sprintf("T%02d:%02d:%02d", 

$dur->hours, 

$dur->minutes, 

$dur->seconds);

with $dur the timestamp difference. i use write_date_time function with format '[h]:mm:ss'. Sometimes my duration is bigger then one day (for istance 36hours and 24minutes 15 seconds) and I don't know how to correctly report this in excel in order to be able to use formula on these cells. I think it's my problem not a library bug

Beste Regards

Alessandro 

[jmcnamara]

Hi Alessandro,

Excel usually wraps hours > 24 into the next day.

You would be better off calculating this as a number as an Excel date number as follows:

    my $date = ( $hours * 3600 + $minutes * 60 + $seconds ) / ( 24 * 60 * 60 );

Then write it using write_number() with a suitable format.

That will handle the wraparound automatically.

John

[ale...@gmail.com]

Thank you John. I'll try it tomorrow morning and report back the results

Regards
Ale

[ale...@gmail.com]

It worked flowerlessly 

Thank you very much