Need Help with Formula for input text when meeting Criteria

[hammerhead13]

I would like for C6 to say "Lunch" if C2 is less than 30min and after that passes C6 to Say "End of Day" if C4 is Less than 30 min. Thank you in advance for the help!


A B C
1 Break 10:00:00 AM
2 Lunch 12:30:00 PM 19:38
3 Break 04:30:00 PM
4 End of Day 05:30:00 PM 00:38
5
6

[jeffs12]

What you're probably looking for is a "nested IF" function. Something like

=IF(C2<30;"Lunch";IF(C4<30;"End of Day";"Neither"))

But before that will work, you'll need to fix the formulas in C2 and C4. You're trying to subtract a date value (the result of the NOW() function) from a time value which is yielding a negative number. Change the format of those cells to 'number' to see what the real result of those formulas are.

[soby]

a word off advice don't use the now() function for calculating working hours

[hammerhead13]

Thank you for the Help. What would be the Best way to capture the current time other than Now()?

[Lupp]

=MOD(NOW();1) will cut off the integer part coding for the date. The reminder returned by MOD is the TOD expressed as the respective fraction of a whole day.

[hammerhead13]

Im not sure but this doesnt seem to be working correctly. I tried changing the format but it is not changing no matter what time i use

[Lupp]

You obviously try to calculate in C2 (e.g.) the time passed since the start of lunch for the current day. Regarding this intention the SIGN of your difference is wrong from the beginning: Even if NOW gave the correct time it was negative after lunch start and positive before.
Start woking with

Code: Select all

=MOD(NOW();1)-B2

in C2 and refine your conditions (positive but less than 00:30:00 e.g. for the short lunch).
As long as the time values are actually numbers (not text) the calculated results never depend on the formats you chose. The only problem with formats is that thay may lie to your face. This will not afflict, however, any conditions based on the values gotten from the respective cells.

(Editing:)
I would prefer a solution like demonstrated in the attached.