Formula Calculate after a certain time of day.

K

Kathy - Lovullo

I have a spreadsheet in which one of the columns has a field which contains
the date & time. I want to put a formula in another column to return a value
of "True" if the time is after 4:30 PM, regardless of the date.

I am having trouble figuring out the correct formula and any help would be
greatly appreciated.
 
D

David Biddulph

=(MOD(A2,1)>TIME(16,30,))
or, if instead of a boolean result you are looking for a text string of
"True", and perhaps blank if the condition isn't satisfied:
=IF(MOD(A2,1)>TIME(16,30,),"True","")
 
F

FSt1

hi
try something like this
date/time in F3
0.6875 formated to time = 4:30pm
=IF(MOD(F3,1)>0.6875,"True","Not")

regards
FSt1
 
P

Peo Sjoblom

With the date plus time in A1


=MOD(A1,1)>TIME(16,30,0)

if you want to include 4:30 PM use


=MOD(A1,1)>=TIME(16,30,0)


note that it will only work if this is a real date with a real time as
interpreted by Excel

using US format


1/12/2008 2:30:00 PM

as an example


if it is text it will always be TRUE since text is always greater than
numbers according to Excel

--


Regards,


Peo Sjoblom
 
D

David Biddulph

Did you mean 16.5, rather than 16.3?

As a matter of interest, why the TIMEVALUE(TEXT(A1,"hh:mm")) rather than
MOD(A2,1)? I had heard that there could be small errors with MOD, but I
thought that this needed very large numbers to cause the error?
--
David Biddulph

try this

=IF(TIMEVALUE(TEXT(A1,"hh:mm"))>16.3/24,TRUE,FALSE)
 
P

Peo Sjoblom

As far as I know the bug in MOD is that it will return the #NUM! error with
very large numbers and small divisors

I believe things like millionth of a decimal is basically nothing but the
regular floating point errors

--


Regards,


Peo Sjoblom
 
Top