PDA

View Full Version : Sleeper: Function Question



gibbo1715
09-15-2005, 04:08 AM
Can anyone help me with the following

Im looking for a function that will look at the contents of a cell and read the time hh:mm

If between 18:00 and 23:59 return result Late
If between 00:00 and 08:00 return result Early
If between 08:01 and 17:59 return result Day

I really would like a function to achieve this please

cheers

gibbo

Marcster
09-15-2005, 04:30 AM
You could use a =VLOOKUP function.
First set up a table (Table range) like so:

18:00 23:59 Late
00:00 08:00 Early
08:01 17:59 Day

The function being:

=VLOOKUP("Cell with the time in","Table range",3)


Marcster

gibbo1715
09-15-2005, 04:37 AM
Hadnt thought of vlook up, ok that will do nicely

thankyou

Gibbo

Bob Phillips
09-15-2005, 04:40 AM
Should be a formula !



Function TimeStatus(rng As Range)
If rng.Count > 1 Then
TimeStatus = "#/Too many cells"
Else
If rng.Value >= TimeSerial(18, 0, 0) Then
TimeStatus = "Late"
ElseIf rng.Value >= TimeSerial(8, 1, 0) Then
TimeStatus = "Day"
Else
TimeStatus = "Early"
End If
End If
End Function

gibbo1715
09-15-2005, 05:09 AM
Looks like this is going to be a bit more complicated than i had thought,

My time period maybe greater than 24 hours so i will need to be able to put 2 days, three day, four days etc.

Also looks like my start date time will be in column E and my finish date time will be in column F format hh:mm dd/mm/yy so i think im gonna go along the lines of vba

Can someone give me a clue as to how to compare two dates and times please so i can start thinking about my case statement

Thanks

Gibbo


Sub Timescale()
Dim StrAmount As Integer
Dim Start As String
Dim Finish As String
Start = Format(Range("F465").Value, "hh:mm dd/mm/yy")
Finish = Format(Range("G465").Value, "hh:mm dd/mm/yy")
'StrAmount = Finish - Start
MsgBox StrAmount
Select Case Length
End Select
End Sub

Bob Phillips
09-15-2005, 05:17 AM
Where are you going with this? Over the course of 3 posts, your requirement seems to have changed from a simple function to read time and put a text status, to a macro that does ... well I don't know what it does.

Can you clearly state the FULL requirement?

gibbo1715
09-15-2005, 05:36 AM
Sorry, the requirement to me has changed a lot since i made my first request and i hadnt seen your previous post till now.

What I now need to be able to do is as follows

I now have a start time date in column F
A finish time date in column g

I need column E to read

Late if the time is less than 24 hours and between 18:00 23:59
Early if the time is less than 24 hours and between 00:00 08:00
Day if the time is less than 24 hours and between 08:01 17:59

If more than 24 hours and less than 48 hours difference then i need it to say one day, same for two days and then more than two days if larger.

I appologise again but my requirements changed quite a bit from what i was originally asked for

Gibbo

shades
09-15-2005, 06:32 AM
Howdy. If you are going to do this with VBA, then try using select case, which should handle your requirements, and allow expansion if needed.

gibbo1715
09-15-2005, 06:36 AM
any chance of an example please

shades
09-15-2005, 06:51 AM
As a pointer, (I just got pulled into a project) Use IF and Select Case


If DayValue < 1 Then
Select Case
Case > 18:00 AND <= 23:59
Case > 00:00 AND <= 08:00
Case > 08:00 AND <= 18:00
End Select
ElseIf
Select Case
Case > 1
Case > 2
Case > 3
End Select
End If


Not tested, but gives an idea.

TonyJollans
09-15-2005, 06:56 AM
I'm not sure what you want to check for the "Early", etc. bits but this might give you something to build on (in E1):


=IF(G1-F1>1,INT(G1-F1)&" days",CHOOSE(INT(MOD(G1-F1,1)*3)+1,"Early","Day","Late"))

I have to go out for a couple of hours but will check back and expand/correct a little if need be

gibbo1715
09-15-2005, 09:24 AM
Thankyou all, i ll have a play and get back to you

Gibbo

Sir Phoenix
09-15-2005, 05:21 PM
Hope I'm not too late to play:


=if(myCell>1/3,if(myCell>2/3,"Nighttime","Evening"),"Day")

Using straight conversions as times being fractions of days.