Time

[Soz]

In one cell I have date/time. For example 27/09/2004 10:00 (cell B4)
In another cell I would like to use the 'IF' function to give the result
that if the time in the cell B4 is before 12:00 then to give result
27/09/2004 08:00 but if time is after 12:00 then to give result 27/09/2004
14:00

Can someone help me with the right formula.
Many thanks
Soz

 

[Jason Morin]

Try:

=IF(MOD(B4,1)<0.5,B4-MOD(B4,1)+8/24,B4-MOD(B4,1)+14/24)

HTH
Jason
Atlanta, GA

 

[Govind]

Hi,

Try

=IF(TEXT(B4,"hh:mm")<"12:00",TEXT(B4,"DD.MM.YY")&"
08:00",TEXT(B4,"DD.MM.YY")&" 14:00")

Regards

Govind.

 

[Jonathan Rynd]

In another cell I would like to use the 'IF' function to give the
result that if the time in the cell B4 is before 12:00 then to give
result 27/09/2004 08:00 but if time is after 12:00 then to give result
27/09/2004 14:00

I don't think either of the other posts does what you said. To do what you
said, with fixed result dates, use:

=DATE(2004,09,27)+IF(HOUR(B4)<12,TIME(08,00,00),TIME(14,00,00))

But if you want the date to be the same date as cell B4, you can use
Jason's formula. I'd write it as:

=INT(B4)+IF(HOUR(B4)<12,TIME(08,00,00),TIME(14,00,00))

 

[AMC]

One way would be to use another cell (C4) next to B4 which would be '=B4'
but in 'General' Format (as all dates are actually stored as numbers).

Before I go any further the following dates equal the numbers stored:
27-09-2004 12:00 = 38257.5
27-09-2004 10:00 = 38257.3333333333 and
27-09-2004 14:00 = 38257.5833333333.

Then in cell D4 you want the following if statment:

=IF(C4=38257.5,38257.5,IF(C4<38257.5,38257.3333333333,38257.5833333333))

This will give you if your C4 says 38257.5 (12:00) then it will return
38257.5, if its either 1 minute before or after 12:00 it will return
38257.3333333333 or 38257.5833333333 respectively.

Finally in cell E4 type '=D4' but use the custom format of dd/mm/yyyy hh:mm.
this will then display either the date and either 08:00 or 14:00.

You can then hide columns C & D to hide the workings.

Hope this helps.

 

[Arvi Laanemets]

Hi


No need for any IF's
=INT(B4)+8/24+(HOUR(B4)>=12)*6/24
or
=INT(B4)+(8+(HOUR(B4)>=12)*6)/24


Arvi Laanemets