Notification texts go here Contact Us Buy Now!

IF time range return "DAY AM", "DAY PM", "NIGHT"

<p>To define time ranges that return "DAY AM", "DAY PM", or "NIGHT", you can use the following formula in Google Sheets:</p>


<p><code><!--[if gte mso 9]><font size="2"><![endif]-->=LET(inttime, INT(SUBSTITUTE(MID(D47, 1, 5), ":", "")), IF(inttime<559, "NIGHT", IF(inttime<1759, "DAY AM", "DAY PM")))<!--[if gte mso 9]></font><![endif]--></code></p>

<p>Here's how this formula works:</p>

<ol>
<li>The <code>MID</code> function extracts the time portion (in the format "hh:mm") from cell D47.</li>

<li>The <code>SUBSTITUTE</code> function removes the colon (:) from the extracted time string.</li>

<li>The <code>INT</code> function converts the resulting string to an integer, representing the time as a whole number (e.g., "06:00" becomes 600).</li>

<li>The <code>IF</code> function checks the value of <code>inttime</code>:
    <ul>
    <li>If <code>inttime</code> is less than 559 (representing 00:00 to 05:59), it returns "NIGHT".</li>

    <li>If <code>inttime</code> is less than 1759 (representing 06:00 to 17:59), it returns "DAY AM".</li>

    <li>Otherwise, it returns "DAY PM".</li>
    </ul>
</li>
</ol>

<p>By applying this formula, you can categorize times into "DAY AM", "DAY PM", or "NIGHT" based on the specified time ranges.</p>

Post a Comment

Cookie Consent
We serve cookies on this site to analyze traffic, remember your preferences, and optimize your experience.
Oops!
It seems there is something wrong with your internet connection. Please connect to the internet and start browsing again.
AdBlock Detected!
We have detected that you are using adblocking plugin in your browser.
The revenue we earn by the advertisements is used to manage this website, we request you to whitelist our website in your adblocking plugin.
Site is Blocked
Sorry! This site is not available in your country.