If statement for time

[Dom]

Hi I have been working on some information that shows when a job was logged
and when it was then completed. I have times for these both and have been
able to work out the difference between these dates in workdays and figure
out the times ( dd:hh:mm) but I now need an if statement for if the time to
only count hours after 9am and before 5pm. For example I want to work out 32
working hours from
09/01/2008 06:53
I have worked out the working days so I currently have
10/01/2008 14:53
but I only want it to work out the 32 hours from 09:00 - 17:00 every day
Is there any way of doing this?

 

[Joel]

use the Hour function

Mytime = TimeValue("09/01/2008 16:53")
If (Hour(Mytime) >= 9) And (Hour(Mytime) <= 17) Then
a = 1
End If

or
=IF(AND(HOUR(B5)>=9,HOUR(B5)<=17),"Yes","No")

these solutions don't include exactly 5:00 PM

Another solution

Mytime = TimeValue("09/01/2008 16:53")
If (Int(Mytime) >= timevalue("9:00 AM")) And (Int(Mytime) <= timevalue(5:00
PM")) Then

End If

=IF(AND(int(B5)>=timevalue("9:00 AM"),Int(B5)<=timevalue("5:00
PM")),"Yes","No")


The Int() rounds the time value to remove the date.

 

[Sandy Mann]

The Int() rounds the time value to remove the date.

Are you sure? Times are a fraction so the INT() is removing the fraction,
(ie time), not the whole number (ie Date)
--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[David Biddulph]

Perhaps Joel intended to use MOD(...,1), rather than INT(...) ?

 

[Dom]

Thank you

use the Hour function

Mytime = TimeValue("09/01/2008 16:53")
If (Hour(Mytime) >= 9) And (Hour(Mytime) <= 17) Then
a = 1
End If

or
=IF(AND(HOUR(B5)>=9,HOUR(B5)<=17),"Yes","No")

these solutions don't include exactly 5:00 PM

Another solution

Mytime = TimeValue("09/01/2008 16:53")
If (Int(Mytime) >= timevalue("9:00 AM")) And (Int(Mytime) <= timevalue(5:00
PM")) Then

End If

=IF(AND(int(B5)>=timevalue("9:00 AM"),Int(B5)<=timevalue("5:00
PM")),"Yes","No")


The Int() rounds the time value to remove the date.

 

[Joel]

To get remove hour portion of time from days it is
MyTime - Int(MyTime)

Not Mod.

For example
6/10/08 = 39609 which is the number of days since Jan 1 , 1900

6:00 AM is .25 which is 6 hours /24 hours

so you have 39609.25

to get the hours from the day it is

39609.25 - Int(39609.25)
39609.25 - 39609

= .25

from
=IF(AND(int(B5)>=timevalue("9:00 AM"),Int(B5)<=timevalue("5:00
PM")),"Yes","No")

to
=IF(AND((B5-int(B5))>=timevalue("9:00 AM"),(B5-Int(B5))<=timevalue("5:00
PM")),"Yes","No")

I guess Mod 1 also works
=IF(AND(Mod(B5,1)>=timevalue("9:00 AM"),Mod(B5,1)<=timevalue("5:00
PM")),"Yes","No")

 

[David Biddulph]

You say "MyTime - Int(MyTime)" but "Not Mod".

What does MOD(39609.25,1) give you, Joel?
What does MOD() do in your version of Excel? Which version are you using?
In my version of Excel (2003), MOD(A1,1) will evaluate to A1-INT(A1),
because more generally, MOD is MOD(n, d) = n - d*INT(n/d)
If you have a different version of MOD(), I would be interested to hear
about it.