Working with "time"

[Regouin]

Hello everyone,

I am having a probably very easy problem here. I am trying to find out in what format VBA sets time and how I can work with times. I just want an easy script that tells the user "good morning", "good afternoon" and "good night".
This is what i have



Dim Dag As String
Dim Tijd
Tijd = Time
If Tijd <= "12:00:00" Then Dag = "Good Morning"
If "12:00:00" < Tijd <= "18:00:00" Then Dag = "Good Afternoon"
If Tijd > "18:00:00" Then Dag = "Good Night"


When I have it like this it tells me all kind of weird stuff, so good night in the morning, etc. But I can't seem to find out how VBA handles times.

TIA
frank

[mdmackillop]

Hi Frank,
Try the following


Sub Greeting()
TimePart = Now() - Int(Now())
Select Case TimePart
Case Is < 0.5
Dag = "Good Morning"
Case Is < 0.75
Dag = "Good Afternoon"
Case Else
Dag = "Good Night"
End Select
End Sub

[Regouin]

Thanks a lot, seems to work, although I have to wait till the afternoon to see if it works because I dont have sufficient rights to change the system time :S

[Regouin]

I have another quick question, do you know how I can get . marks in numbers so when I ask VBA to get a certain value from a cell (for instance 3158392) how I can display it as 3.158.392, I know I can work my way around it with contatenate and right-function, but is there an easier function for this?

[Bob Phillips]

I have another quick question, do you know how I can get . marks in numbers so when I ask VBA to get a certain value from a cell (for instance 3158392) how I can display it as 3.158.392, I know I can work my way around it with contatenate and right-function, but is there an easier function for this?

Assuming in cell A1, try


Format(Range("A1").Value,"#\.##0.00")

[Bob Phillips]

[QUOTE=mdmackillop]


TimePart = Now() - Int(Now())

/QUOTE]

You can use Time to avoid having to strip the days from the Now function.

[Regouin]

Thanks for the help but now I think you edit the format of the cell itself and I just want VBA to display the numbers with 1000 marks.

[Bob Phillips]

Thanks for the help but now I think you edit the format of the cell itself and I just want VBA to display the numbers with 1000 marks.

All I gave you was the syntax, I didn't suggest how to deply it. It will format the cell if yo do that


Range("A1").Value = Format(Range("A1").Value,"#\.##0.00")

or you can just MsgBox it


MsgBox Format(Range("A1").Value,"#\.##0.00")

or whatever you want.

[Regouin]

Yup, got it now.

Only changed it a bit because I dont need any decimals



MsgBox Format(Range("A1").Value,"#\.##0")


Now it works perfect

thanks

Frank

[Bob Phillips]

Only changed it a bit because I dont need any decimals

Frank, from your OP, you probably need



MsgBox Format(Range("A1").Value,"#\.###\.##0")

[Regouin]

Since it is now afternoon here, i can tell you that it works perfectly. Thanks again

Frank