Calculated Date Time field with 12 AM not recognized as 0

[jbrown19]

Hi everyone, I am looking for help with an issue I am encountering. My goal is to be able to identify when 12 AM is in a date/time so I can apply a different calculation in a formula. I have a date time field that is the result of a calculation, if the field ends up with 12 AM (example: 10/29/24 12:00 AM) Excel is not recognizing it as a 0. If I MOD the field it shows 0 but if I use an IF statement in another cell to compare it to 0 it results in false. Now if I go back and type in a date time with 12 am in the field that was originally a calculated field the result is True. See below for reference. Cells with formulas applied are shown in the top row. Please note all date time fields are formatted the same. Any help would be greatly appreciated! Thanks, Jeff

 

[JamesCanale]

I suggest it's a rounding oddity. Sometimes that appears in excel. Something like 8.0 is coming out at 8.00000001 (when it shouldn't). Try rounding it off a bit - that might sort it out.

 

[jbrown19]

I suggest it's a rounding oddity. Sometimes that appears in excel. Something like 8.0 is coming out at 8.00000001 (when it shouldn't). Try rounding it off a bit - that might sort it o

I suggest it's a rounding oddity. Sometimes that appears in excel. Something like 8.0 is coming out at 8.00000001 (when it shouldn't). Try rounding it off a bit - that might sort it out.

Awesome, that worked! Greatly appreciate the quick response! Have a great day!

 

[KillerOfGiants]

With the date in the same field as time, it won't be 0. Excel stores date/time entries as follows: the number before the decimal is the date (i.e.: 45594 is 10/29/2024). The number after the decimal is time. So 45594 with a mod of 0 means Oct 29, 2024 at 12 am. Whereas 45594.04167 would be "10/29/2024 1:00 AM".