simple "AND" issue

[sassriverrat]

Good Afternoon,

I'm having an issue- I think I've coded "AND" wrong.
Right now, with today's date and time, the sheet is kicking a "No" when it should be "Yes" and changing the computer date to December (to see if it says "Yes") doesn't change away from "No" so I figure my use of "AND" was wrong. Thanks for the help!

This is designed to provide a "Yes" or a "No" to state if daylight savings is in effect or not.
Notes!L13 is the date of daylight savings start (March 10, 2019)
Notes!M13 is 2:00 AM
Notes!L14 is the date of daylight savings end (November 3, 2019)
Notes!M14 is 2:00 AM
C5 is today's date
D5 is the current time

The cells in Notes automatically update annually.

=IF(AND((NOTES!L13+NOTES!M13)<((C5+D5)),(NOTES!L14+NOTES!M14)>((C5+D5))),"YES","NO")[\code]

 

[sassriverrat]

I'm sorry. First, you were talking about time entered as 12:34, not 1234. And in that case, you are correct: the formula is wrong. In both cases, "<=0" should be "< 1". Klunk!

well earlier I put in "1500" and it popped out 15:00 but then later I put in "1000" and it popped 00:01 back out... I can't figure out why.

As for the military to time functions I have (which I had forgotten about), they are designed to do the same thing that yours does, and I'm not sure if there is an instance in which they might work with a time input where yours might now- so kudos to being good! They were designed to handle just about any method of writing a time into a cell that I could think of so at to mitigate the risk of a user producing an error in the sheet. I have one user that is particularly fantastic at screwing up excel sheets so this seemed to solve that.

As for the original problem, I miss the fact that K13 and L13 on the Notes sheet were merged....but the K column had been reduced in size and I didn't notice it so naturally when the formula looked for a date in L13, it wasn't reading the date in the cell but instead reverted to a 1/1/1900 date which obviously through my whole formula off....just changing L13 to K13 in my formulas fixed all of the problems!

 

[joeu2004]

I put in "1000" and it popped 00:01 back out... I can't figure out why.

I presume that you entered the numbers without quotes. When I enter 1000, both formulas convert it to 10:00 when the time format is hh:mm .

If you can duplicate the 00:01 result, I suggest that you upload an Excel file (redacted) that demonstrates (just) that problem to a file-sharing website, and post the public/share URL here. First, test the download URL, being careful to log out of the file-sharing website in order to verify that the URL can be accessed anonymously. If you don't have a favorite file-sharing website, I like box.net/files. But ignore any preview errors when you try to download.

BTW, be sure to enter midnight as 0000 or 00:00, not 2400 or 24:00.