Time and Yes/No

%3CLINGO-SUB%20id%3D%22lingo-sub-1283570%22%20slang%3D%22en-US%22%3ETime%20and%20Yes%2FNo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1283570%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20Date%2FTime%20Field%20named%20%22Submitted%22%20in%20format%20mm%2Fdd%2Fyyyy%20hh%3Amm.%26nbsp%3B%20I%20have%20another%20calculated%20field%20named%20%22HelperTime%22%20with%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%3DTEXT(Submitted-DATE(YEAR(Submitted)%2CMONTH(Submitted)%2CDAY(Submitted))%2C%22hh%3Amm%22)%3C%2FP%3E%3CP%3EThis%20gives%20me%20just%20the%20time%20from%20the%20%22Submitted%22%20field.%26nbsp%3B%20I%20would%20like%20to%20have%20a%20calculated%20Yes%2FNo%20field%20that%20would%20return%20the%20value%20%22yes%22%20if%20%22HelperTime%22%20is%20greater%20that%2014%3A00%20and%20%22no%22%20otherwise.%26nbsp%3B%20I%20am%20using%20the%20following%20formula%20but%20it%20is%20not%20working%3A%3C%2FP%3E%3CP%3E%3DIF(HelperTime%26gt%3B14%2C%22Yes%22%2C%22No%22)%3C%2FP%3E%3CP%3EIf%20i%20use%2014%3A00%20in%20above%20formula%20it%20get%20an%20error%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1283570%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETime%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1337027%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20and%20Yes%2FNo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337027%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F80138%22%20target%3D%22_blank%22%3E%40Rob%20Nunley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooks%20like%20you%20helper%20time%20is%20a%20text%20string%2C%20so%20you%20need%20to%20convert%20it%20to%20time.%26nbsp%3B%20Try%3A%3C%2FP%3E%3CP%3E%3DIF(TIME(LEFT(Helpertime%2C2)%2CRIGHT(Helpertime%2C2)%2C0)%26gt%3BTIME(14%2C0%2C0)%2C%22Yes%22%2C%22No%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I have a Date/Time Field named "Submitted" in format mm/dd/yyyy hh:mm.  I have another calculated field named "HelperTime" with the following formula:

=TEXT(Submitted-DATE(YEAR(Submitted),MONTH(Submitted),DAY(Submitted)),"hh:mm")

This gives me just the time from the "Submitted" field.  I would like to have a calculated Yes/No field that would return the value "yes" if "HelperTime" is greater that 14:00 and "no" otherwise.  I am using the following formula but it is not working:

=IF(HelperTime>14,"Yes","No")

If i use 14:00 in above formula it get an error message.

 

1 Reply
Highlighted

@Rob Nunley 

Looks like you helper time is a text string, so you need to convert it to time.  Try:

=IF(TIME(LEFT(Helpertime,2),RIGHT(Helpertime,2),0)>TIME(14,0,0),"Yes","No")