Elapsed time calculations from imported data

[JariV]

Hi,

I've been using Filemaker for few weeks now for relatively simple tasks, but I haven't done any time calculations yet - they seem a bit tricky for a newbie...(This issue has probably been addressed here before, but I couldn't find any previous posts that would help...sorry about possible repetition..)

Here's my problem:

I'm importing data from an Excel-compatible file (.ddb), including two different date/time values in the following format:

Start: yyyy-mm-dd hh:mm (for example: "2004-03-26 14:25", one field)

End: yyyy-mm-dd hh:mm (one field)

(These can be on the same day or extend to the next day)

From this data I'm trying to calculate:

- elapsed time (hh:mm)

- additionally, I should calculate elapsed time between 18:00 and 06:00 (for additional night bonus)

First, I know how to calculate elapsed time from two separate Date/Time values (EndTime-StartTime + (EndDate-StartDate) * 86400)), but I haven't yet figured out how to do that with the imported data as it is (or is it possible at all?) - do I need to separate Date and Time from the imported data for the calculations, and what is the best way to do it?

Second, when the EndTime is on the same day as the StartTime, calculating the elapsed bonus time (between 18:00 and 06:00) seems doable (when using separate Date and Time values). But when the EndTime is on the next day, that's where I'm having some sort of mental block...

Any help with this would be greatly appreciated,

Jari V.

FileMaker Version: 6

Platform: Windows XP

[CyborgSam]

Jari->

Welcome to the forums!

FileMaker keeps time and Date separate, so yes, you will need two fields.

The first step is to construct calculations to parse the text. Let's assume the field from Excel is called ExcelText and the format is exactly yyyy-mm-dd hh:mm where that's a space between dd & hh.

the Date calculation: TextToDate( Middle( ExcelText, 6, 2) & "/" & Middle( ExcelText, 9, 2) & "/" & Left(ExcelText, 4))

the Time calculation: TextToTime( Right( ExcelText, 5))

One method would be to simply have two calculation fields. The disadvantage is that calculation fields cannot be modified, so if these dates & times change this would be a problem. Another would be to create a fields for the date and time, then use the calculations in two Replace Contents (with calculated result) script steps with the modifyable Date & Time fields.

The script steps looks like this:

Replace Contents [No dialog, "ExcelDate", "TextToDate( Middle( ExcelText, 6, 2) & "/" & Middle( ExcelText, 9, 2) & "/" & Left(ExcelText, 4))"]

Replace Contents [No dialog, "ExcelTime", "TextToTime( Right( ExcelText, 5))"]

Will that do what you need?

Sam

[JariV]

Thanks a lot Sam,

I managed to separate Date and Time from the exported data and now the calculation for (total) elapsed time seems to be working fine.

I still need to figure out how to extract the elapsed time between 18:00 and 06:00 out of the total elapsed time...

Jari

[Lee Smith]

[color:"blue"]> I still need to figure out how to extract the elapsed time between 18:00 and 06:00 out of the total elapsed time...

A simple calculation to this would be:

((End Date - Start Date) * 86400) + Time Out16:00 - Time In

However, this does not consider weekends or holidays.

Lee

FileMaker Version: 6

Platform: Mac OS 9