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]

 

[joeu2004]

The logic of your AND expression works fine for me, although I would change the first condition to "<=".

I wonder if D5 is not really just "current time". Did you use =NOW() (date as well as time of day) or =MOD(NOW(),1) (only time of day)?

Even the latter might be wrong for your purposes. It would be prudent to write =--TEXT(NOW(), "h:m:s").

PS.... Why separate date and time into two cells? Why not simply enter March 10, 2019 2:00 AM into one cell? Similarly for the other date and times.

 

[sassriverrat]

So C5 and D5 are manually-input dates and times. However, D5 may be put in as 0200 or 2:00 (just a random time picked). Would that throw it?

 

[sassriverrat]

So here's another formula on another sheet throwing the same issue-

note- I will fix the >= you marked AND the "C5" and "D5" cells from before are on the "Voyage Specifics" Tab

=IF(AND((Notes!L13+Notes!M13)<((IF(F4="NO DATA INPUT",'VOYAGE SPECIFICS'!C6+'VOYAGE SPECIFICS'!C7,F4+D4))),(Notes!L14+Notes!M14)<((IF(F4="NO DATA INPUT",'VOYAGE SPECIFICS'!C6+'VOYAGE SPECIFICS'!C7,F4+D4)))),"YES","NO")

 

[sassriverrat]

PS.... Why separate date and time into two cells? Why not simply enter March 10, 2019 2:00 AM into one cell? Similarly for the other date and times.

To answer your question- because I have formulas that auto calculate daylight savings in the date cells (time cells are static) and they spit out the date- so I don't have to change it on every user's computer every year

 

[joeu2004]

D5 may be put in as 0200 or 2:00 (just a random time picked). Would that throw it?

Certainly! Excel does not recognize 0200 as time of day. A "quick fix" (not a good one): change C5+D5 to C5+IF(D5<=0,D5,TEXT(D5,"0\:00")).

Better: Convert D5 using =IF(D5<=0,D5,--TEXT(D5,"0\:00") in E5, then use C5+E5. Note the need to use "--" (double negate) with TEXT in that context.

 

[joeu2004]

=IF(AND((Notes!L13+Notes!M13)<((IF(F4="NO DATA INPUT",'VOYAGE SPECIFICS'!C6+'VOYAGE SPECIFICS'!C7,F4+D4))),
(Notes!L14+Notes!M14)<((IF(F4="NO DATA INPUT",'VOYAGE SPECIFICS'!C6+'VOYAGE SPECIFICS'!C7,F4+D4)))),"YES","NO")

I don't see any difference vis-a-vis the key problem: the form of the time of day.

An aside.... The use of redundant parentheses obfuscates the design of the formula, IMHO. Consider writing it as:

=IF(AND(Notes!L13 + Notes!M1 <= IF(F4="NO DATA INPUT", 'VOYAGE  SPECIFICS'!C6 + 'VOYAGE  SPECIFICS'!C7, F4+D4),
Notes!L14 + Notes!M14 < IF(F4="NO DATA  INPUT", 'VOYAGE SPECIFICS'!C6 + 'VOYAGE SPECIFICS'!C7, F4+D4)), "YES", "NO")

And consider using the null string ("") or simply an empty cell instead of "NO DATA INPUT". Then you can write IF(F4="",...).

 

[sassriverrat]

So that seemed to work for me if I put the time in as 1500 but if I put it in as 15:00 then it didn't work.

Now on a side note- I found my issue......haha wonderful. Where I had Notes!L13, it should have been Notes!K13. Come to notice that the K tab was merged and the tab itself was very very small....so checking the coding it all works with K in place of L!

For your info- here's the bit of coding I had before (that I came co-created with another forum user a while back) for the military time piece-

Function Miltime(T1 As Integer)' Input T1: 24-hour time as integer,e.g., 1130=11:30, 1650=16:50
' Output, time as serial time e.g, 0.5 for noon.


Dim TT1 As Double
TT1 = Int(T1 / 100) + (((T1 / 100) - Int(T1 / 100)) / 0.6) '23.50
TT1 = TT1 / 24
Miltime = TT1


End Function


Function MilitarytoTime(Miltime As String) As Date
If Miltime <> "" Then
    MilitarytoTime = Format(Replace(Miltime, ":", ""), "00:00")
End If
End Function
'Public Function MilitaryToTime(d As Double) As Double
  'If d < 1# Then
  ' ' MilitaryToTime = d
  'Else
  '  MilitaryToTime = CDate(Format(d, "0\:00"))
 ' End If
'End Function

 

[joeu2004]

So that seemed to work for me if I put the time in as 1500 but if I put it in as 15:00 then it didn't work.

What doesn't work when you enter 15:00?

It works just fine when I enter =TODAY() into C5 and the number 1234 into D5. The result of the formula =C5+IF(D5<=0,D5,TEXT(D5,"0\:00")) is 3/20/2019 12:34 when properly formatted.

The formula =IF(D5<=0,D5,--TEXT(D5,"0\:00")) also works when you correct my "obvious" typo of a missing right-parenthesis. The result is 12:34 when propertly formatted.

I convert military time (1500) to Excel time (15:00). That is consistent with the Excel formula that you posted.

Your VBA code is expecting military time; that is, time as an integer (100*h + m). (FYI, it is better to use type Long.)

OTOH, it is not clear to me that you need that function. It seems to be some kind of conversion of military time -- perhaps the conversion that I do in the Excel formula. However, I am not taking the time to understand your code.

I cannot help you further if you present the problem(s) in pieces without explanation.

 

[joeu2004]

It works just fine when I enter =TODAY() into C5 and the number 1234 into D5. The result of the formula =C5+IF(D5<=0,D5,TEXT(D5,"0\:00")) is 3/20/2019 12:34 when properly formatted. The formula =IF(D5<=0,D5,--TEXT(D5,"0\:00")) also works when you correct my "obvious" typo of a missing right-parenthesis. The result is 12:34 when propertly formatted.

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!