Date with time calculation

[Henry]

Hi there, now i'm creating a task related function. The sample file i attatch have 6 field (Duration, DurationType, StartDate, StartTime, FinishDate, FinishTime). The FinishDate and FinishTime is calculation field. The DurationType contains a value list (Minute(s), Hour(s), Day(s), Week(s), Month(s), Year(s)). User will fill in the first 4 field and then the system will calculate the Finish date and time.

For example if duration is 1 Day(s) and the start date is 12/25/03, then the finish date will be 12/26/03. The time calculation is based on minute and hour.

If user fill in Startdate 12/25/03, time 8:00PM and duration is 7 Hour(s). By right the result would be 12/26/03, 3:00AM. But i can't find a solution to get this result. I only can change the time but the date remain the same.

Regards,

Henry

Date&Time.zip

[ESpringer]

This is a fun puzzle. I'm just sketching a thought, for lack of time:

First, I'd recommend reducing all durations, via calculation, to number of minutes (there are 1440 in a day). That will allow you to expand your solution so that you could let users enter "1 month and 3 days and 1 hour" and you could still tally the whole thing... Anyway, this field (call it cDurationMinutes, say) will help with solving your problem:

You need to calculate the number of minutes left in the day on which the event is starting. Call it cMinTilMidnight field...

The trick is to divide cDurationMinutes by 1440 AFTER subtracting out the minutes required to get to midnight.

Use Int((cDurationMinutes - cMinTilMidnight)/1440) to get the number of full days to add onto your start date.

Use Mod((cDurationMinutes - cMinTileMidnight)/1440) to get how many minutes are "left over" to spill into that day, for calculating the time.

The rest should be easy