If statement for time
 

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?

If statement for time
 

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.
"Dom" wrote:

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?

If statement for time
 

"Joel" wrote in message
...


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


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


"Joel" wrote in message
...
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.
"Dom" wrote:

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?

If statement for time
 

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

"Sandy Mann" wrote in message
...
"Joel" wrote in message
...


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


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


"Joel" wrote in message
...
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.
"Dom" wrote:

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?

If statement for time
 

Thank you

"Joel" wrote:

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.
"Dom" wrote:

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?

If statement for time
 

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")


"Dom" wrote:

Thank you

"Joel" wrote:

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.
"Dom" wrote:

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?

If statement for time
 

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.
--
David Biddulph

"Joel" wrote in message
...
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")


"Dom" wrote:

Thank you

"Joel" wrote:

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.
"Dom" wrote:

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?