Dear All

I have a worksheet which has a start time and an end time for visits
throughout the week, Some lines however are blank

I want a formula which tells me if the end of the visit is before 9.30 am to
return "morning", if its before 17:00 then return "Afternoon", if its before
23:30 return "Evening" and if its blank return "Ignore"

Could anyone help me please ?

D

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200810/1

There seems to be some *holes* in your requirements like what if the end
time is after 9:30am but before 12 noon? and what if it is after 23:30?

=IF(B1<=9.5/24,"Morning",IF(B1<=17/24,"Afternoon",IF(B1<23.5/24,"Evening","Nighshift?")))

If there will never be any ent time after 23:30 then you can cut it down to:

=IF(B1<=9.5/24,"Morning",IF(B1<=17/24,"Afternoon","Evening"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"dazoloko via OfficeKB.com" <u43760@uwe wrote in message
news:8b9ba69d3ea41@uwe...
Dear All

I have a worksheet which has a start time and an end time for visits
throughout the week, Some lines however are blank

I want a formula which tells me if the end of the visit is before 9.30 am
to
return "morning", if its before 17:00 then return "Afternoon", if its
before
23:30 return "Evening" and if its blank return "Ignore"

Could anyone help me please ?

D

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200810/1

=IF(A2<TIME(9,30,),"morning",IF(A2<TIME(17,,),"Aft ernoon",IF(A2<TIME(23,30,),"Evening",IF(A2="","Ign ore","After
evening"))))
I am assuming that your cells contain just a time, not a date and time. If
the latter, replace each instance of A2 in the formula by MOD(A2,1).
--
David Biddulph

"dazoloko via OfficeKB.com" <u43760@uwe wrote in message
news:8b9ba69d3ea41@uwe...
Dear All

I have a worksheet which has a start time and an end time for visits
throughout the week, Some lines however are blank

I want a formula which tells me if the end of the visit is before 9.30 am
to
return "morning", if its before 17:00 then return "Afternoon", if its
before
23:30 return "Evening" and if its blank return "Ignore"

Could anyone help me please ?

D

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200810/1

Thank you both for the repsonses, between the two I got it nailed.

Thanks for your time.

D


David Biddulph wrote:
=IF(A2<TIME(9,30,),"morning",IF(A2<TIME(17,,),"Af ternoon",IF(A2<TIME(23,30,),"Evening",IF(A2="","Ig nore","After
evening"))))
I am assuming that your cells contain just a time, not a date and time. If
the latter, replace each instance of A2 in the formula by MOD(A2,1).
--
David Biddulph

Dear All

[quoted text clipped - 10 lines]

D

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200810/1