Issue with Calculating End Time Using XMP:CreateDate and Duration

[lei]

Hi Experts,

I'm using the following command to extract the creation date, duration, and calculate the end time for AVI files:

Code Select Expand

exiftool -n ^
   -p "${XMP-xmp:CreateDate},${Duration;$_=$_/86400},${XMP-xmp:CreateDate;DateFmt('%%s');$_=$_+$self->GetValue('Duration')/86400}" ^
   -ext avi . > out.csv

My goal is to output the creation date, duration, and the end time (creation date + duration). However, the end time calculation seems incorrect, and I'm unsure how to properly convert or handle the data to get the correct result.

Could someone help clarify what might be wrong with my end time calculation and suggest the correct way to convert or compute it? Specifically, I need the end time to be the creation date plus the duration, formatted appropriately.

Thank you!

[Phil Harvey]

Is this command in a cmd BAT file?  It seems so because you have doubled the percent character.

I tried this command and it does what I expect.  Could you show an example output?

- Phil

[StarGeek]

I'm having trouble with the math. Why divide the duration by the number of seconds in the day?  And then add that fraction to the CreateDate?

Using a 46 second video, dividing the duration by 86,400, ends up with 0.000534716875, which is then added to the CreateDate. That does not output the end time of the video.

It seems to me that the command should be something like this
exiftool -n -p "${XMP-xmp:CreateDate},${Duration},${XMP-xmp:CreateDate;ShiftTime('0:0:'.$self->GetValue('Duration','ValueConv'))}" -ext avi . > out.csv

[lei]

Hi Phil,

Thank you for your quick reply!

Yes, the command is indeed in a BAT file.

The output I'm getting looks something like this:

Code Select Expand

2003:02:10 07:21:27Z,0.000255555555555556,1044861687.00026

When I open the output in Excel, I can format the second column (duration) as "hh:mm:ss.00", which displays as 00:00:22.08. However, the third column (end time) doesn't seem to be a valid Excel date/time number, and I'm unsure how to format it to display as a human-readable date and time, such as 2003-02-10 07:21:49.

[Phil Harvey]

The 3rd column is the number of seconds since the epoch ('%s' format code) plus the duration in seconds divided by 86400.  StarGeek is correct.  This math doesn't make sense.  I think his example should shed some light on this.

- Phil

[lei]

Hi there!

Thank you both for your help!

StarGeek's command work perfectly — sorry about my last reply. I didn't see your reply before I posted. I was working from an older browser tab and forgot to refresh the page.

The reason I divided the Duration value by 86400 was to convert seconds into days, because Excel displays dates and times as day-based floating-point values. I did this in the second column for Excel compatibility — and then just copied the logic into the third column without thinking much about whether it was really necessary there

Thanks once again!