Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If statement to compare time cell to a time | Excel Worksheet Functions | |||
If statement for time | Excel Discussion (Misc queries) | |||
if then statement = time | Excel Worksheet Functions | |||
If statement with time | Excel Discussion (Misc queries) | |||
IF statement to calculate time usage in specific time bands | Excel Worksheet Functions |