IF statement with time and date.

[Jtucker10278]

I have found a fair amount of information regarding how to deal with time values independent of the date, but not much information when the date it attached.

I am trying to write a formula that is

=If(A1>4:00pm, 4:00pm, A1)

but the twist is that A1 has the value of 6/6/2024 6:50:00 PM

and value if true needs to hold the date and just adjust the time to 4:00 pm

I don't care if I have to break this out over several columns to get it right, I have been trying to pull apart the date and time (and then time values (h,m,s) and then do my if statement then build it back to the time date but that isnt working either.

not sure if it matters but the Date and Time value is stored in a cell with a custom number format of hh:mm:ss AM/PM

any help is appreciated.

 

[Micron]

Since dates are floating point double data type, try subtracting the integer portion from the date value and compare to that result.
This Now() value: 11/23/2024 11:14 is actually 45619.469147 to 6 decimal places. If you subtract the Int() portion of that number you'll be left with 0.469147 which represents time as a fraction of a day (24 hours). The portion left of the decimal is, as you may know, the number of days since the start of your system date option.

 

[MARK858]

Do you mean something like....

Book1
	A	B	C	D	E
1	23/11/2024 16:26				23/11/2024 16:00
2	23/11/2024 08:26				23/11/2024 08:26
Sheet6

Cell Formulas
Range	Formula
E1:E2	E1	=IF(MOD(A1,1)>TIMEVALUE("4:00 pm"),DATE(YEAR(A1),MONTH(A1),DAY(A1) )+TIMEVALUE( "4:00 pm"),A1)

or

Book1
	A	B	C	D	E
1	23/11/2024 16:26				23/11/2024 16:00
2	23/11/2024 08:26				23/11/2024 08:26
Sheet6

Cell Formulas
Range	Formula
E1:E2	E1	=IF(MOD(A1,1)>TIMEVALUE("4:00 pm"),INT(A1)+TIMEVALUE( "4:00 pm"),A1)

 

[Cubist]

One more option:

=INT(A1)+MIN(2/3,MOD(A1,1))

 

[Jtucker10278]

Cubist - I don't understand what that is, or what it is doing. But I know enough to know that it is beautiful and it works. would you mind walking me through how/what that is doing.

Mark - The second solution is the type of thing I had in mind. but I don't understand the first solution. I see that it is dealing with the whole range instead of row by row but I don't know what the purpose is, when/why would I use a solution like that if it is giving the same outcome?

But thank you to both you have solved my problem and if you take the additional time to explain more so that I can continue to learn I thank you again.

 

[MARK858]

Neither of the formulas I posted deal with any range other than A1, with the formula dragged down to cover A2. The only difference between the 2 formulas is how they get the Date part from A1 (the 2nd one is more efficient)

Hint: Cubist formula is based on 4pm being 2/3 of a day (16/24)

 

[Cubist]

would you mind walking me through how/what that is doing.

In Excel, dates and times are stored as serial numbers:

• The integer part represents the date (e.g., 45000 = July 1, 2023) - number of days from 1/0/1900.
• The decimal part represents the time of day (e.g., 0.5 = 12:00 PM).
• INT(A1) extracts the integer part of the datetime value, removing the time and leaving only the date.
  Example: If A1 = 45000.75, then INT(A1) = 45000 (date only).
• MOD(A1,1)gives the fractional part of A1, isolating the time.
  • If A1 = 45000.75, then MOD(A1,1) = 0.75 (time as a fraction of the day).
• MIN(2/3, MOD(A1,1)):
  This compares 2/3 (which is 16/24, equivalent to 4:00 PM) with the fractional time (MOD(A1,1)) and returns the smaller of the two.

 

[Jtucker10278]

I understood the 16/24 part but i had no clue you could reduce that fraction the 2/3 completely confused me.

does that work because the fraction is always going to be ##/24 and therefore no other numbers of ##/24 could reduce to 2/3.

Thanks again

 

[Cubist]

does that work because the fraction is always going to be ##/24 and therefore no other numbers of ##/24 could reduce to 2/3.

Not exactly.
In general, you can reduce fractions by finding the greatest common factors of the two numbers. In this case, 16 and 24 are both divisible by 8, thus 16/8 = 2 and 24/8 =3.
Another example, 12/24 the greatest common factor here is 12 so 12/12 = 1 and 24/12 = 2 so you get 1/2 or 0.5.

You can stick with 16/24 if the 2/3 confuses you. Makes no difference in the result.