IIf Statement with a date/time field

[Liz_I3]

Hello
I have a date/time field called Message Date/Time and another field called IN I want to create a new field called Time IN and have with this : Time In: IIf([IN]="IN",[Message Date/Time],"") It does give me the correct result but as text. I want to keep it as a date and time so that I can do calculations. I tried TimeValue which does work except if there is not date if gives #Error and then the calculations don't work either.

My end result is I have a start date/time and an end date/time and I want to subtract the 2 to get total time spent. But both start and end date/times have numerous entries per day so for the start time I need the first time in and for the End time the last time out

 

[ashakantasharma]

You can format the date and time using =DATE & =TIME for using them and use a combination =SUMPRODUCT with MAX function for each day to get the end time for each day and use =MIN function to get first time IN for each day...

 

[welshgasman]

You can format the date and time using =DATE & =TIME for using them and use a combination =SUMPRODUCT with MAX function for each day to get the end time for each day and use =MIN function to get first time IN for each day...

@*[B]ashakantasharma[/B]

This is an Access forum?

 

[welshgasman]

Have you tried 0 for the date if not in?
Dates are helds as numbers internally.

 

[ashakantasharma]

@*[B]ashakantasharma[/B]

This is an Access forum?

Extremely Sorry for the blunder......These days I only see EXCEL, EXCEL and EXCEL everywhere .....need to correct it immediately...