Calculation using a time field

[ML2008]

How can I return the result "Evening" if the TimeField is >17:00 and <24:00 ?

[ML2008]

Or better yet, how can i define the times for Morning, Noon, Afternoon and Evening on a costum function

[comment]

Try something like:

Let ( [

h = Hour ( EntryTime )

] ;

Case (

h < 9 ; "Night" ;

h < 12 ; "Morning" ;

h < 19 ; "Noon" ;

h < 23 ; "Evening" ;

"Night"

)

)

[Transmasco]

Sorry to Hijack the thread but I need a similar calc.

I have 2 time fields: StartTime and EndTime (both within the same day). I have a field ElapsedTime=(EndTime - StartTime).

I need to break up the elapsed time into 3 different fields:

NormalWorkHours = Time within 8:00 and 12:15; 13:00 and 17:00

DaytimeExtraHours= Time within 7:00 and 8:00; 17:00 and 19:00

NighttimeExtraHours= Time within 0:00 and 7:00; 19:00 and 24:00

So that if StartTime = 5:00 and EndTime = 20:00, Id get:

ElapsedTime = 14:15

NighttimeExtraHours = 2:00+1:00=3:00

DaytimeExtraHours = 1:00+2:00=3:00

NormalWorkHours = 4:15 + 4:00=8:15

I've been going nutz trying to figure this one out. Any help will be greatly appreciated.

Edited March 14, 2006 by Guest

[comment]

Your example does not add up:

20 - 5 = 15

How can you end up with more than 16 hours in total?

[Transmasco]

Fixed. Sorry, missinterpreted 20:00 for 10pm :)

[comment]

Try this:

NighttimeExtraHours =

Let ( [

rangeStart = Time ( 0 ; 0 ; 0 ) ;

rangeEnd = Time ( 7 ; 0 ; 0 )

] ;

Max ( Min ( EndTime ; rangeEnd ) - Max ( StartTime ; rangeStart ) ; 0 )

)

+

Let ( [

rangeStart = Time ( 19 ; 0 ; 0 ) ;

rangeEnd = Time ( 24 ; 0 ; 0 )

] ;

Max ( Min ( EndTime ; rangeEnd ) - Max ( StartTime ; rangeStart ) ; 0 )

)

For the other two fields, juat replace the 4 range parameters.

[Transmasco]

[in tears] I love you Comment!

Works like a charm!