Check If A Time Is Between Two Other Times Including Minutes Hacked

May 17, 2025 by ADMIN 68 views

Check if a Time is Between Two Other Times Including Minutes Hacked

===========================================================

Introduction

When working with dates and times in Power Apps, it's often necessary to check if a specific time falls within a certain range. This can be particularly useful when creating forms that require users to submit data within a specific timeframe. In this article, we'll explore how to create a formula for a calculated column that checks if a time is between two other times, including minutes hacked.

Understanding the Problem

Let's assume you have a form with an adjustable "Submission Date" field, as shown in the screenshot below:

[Insert Screenshot]

As you can see, the form has a field for the submission date, which includes both the date and time. You want to create a formula that checks if the submitted time falls within a specific range, say between 9:00 AM and 5:00 PM, including minutes hacked.

Calculated Column Formula

To achieve this, you'll need to create a calculated column in your Power Apps form. A calculated column is a formula-based column that can be used to perform calculations and logical operations on data. In this case, you'll use the calculated column to check if the submitted time falls within the specified range.

Here's an example formula that you can use:

= IF(
    AND(
        TIMEVALUE(SubmissionDate) >= TIMEVALUE("9:00 AM"),
        TIMEVALUE(SubmissionDate) <= TIMEVALUE("5:00 PM")
    ),
    "Time is within range",
    "Time is outside range"
)

Let's break down this formula:

TIMEVALUE function is used to convert the date and time string into a time value that can be compared.
AND function is used to check if both conditions are true.
>= and <= operators are used to check if the submitted time is greater than or equal to 9:00 AM and less than or equal to 5:00 PM, respectively.
IF function is used to return a value based on the result of the AND function.

Adjusting the Formula for Minutes Hacked

However, the above formula does not take into account minutes hacked. To adjust the formula to include minutes hacked, you can use the following formula:

= IF(
    AND(
        TIMEVALUE(SubmissionDate) >= TIMEVALUE("9:00 AM"),
        TIMEVALUE(SubmissionDate) <= TIMEVALUE("5:00 PM")
    ),
    "Time is within range",
    "Time is outside range"
)

But this formula will not work as expected because it does not take into account the minutes hacked. To fix this, you can use the following formula:

= IF(
    AND(
        HOUR(SubmissionDate) >= 9,
        HOUR(SubmissionDate) <= 17,
        MINUTE(SubmissionDate) >= 0,
        MINUTE(SubmissionDate) <= 59
    ),
    "Time is within range",
    "Time is outside range"
)

This formula uses the HOUR and MINUTE functions to extract the hour and minute values from the submitted time, and then checks if they fall within the specified range.

Conclusion

In this article, we've explored to create a formula for a calculated column that checks if a time is between two other times, including minutes hacked. We've also discussed how to adjust the formula to include minutes hacked. By using the calculated column formula, you can easily check if a submitted time falls within a specific range, making it easier to manage and analyze data in your Power Apps form.

Additional Tips and Variations

Here are some additional tips and variations that you can use to customize the formula:

Using a specific date range: Instead of using a fixed date range, you can use a specific date range by replacing the "9:00 AM" and "5:00 PM" values with the start and end dates of the range.
Using a different time format: If you're using a different time format, such as 24-hour format, you can adjust the formula accordingly.
Using a different condition: Instead of checking if the time is within a specific range, you can use a different condition, such as checking if the time is before or after a specific time.

Common Issues and Solutions

Here are some common issues and solutions that you may encounter when using the calculated column formula:

Error: "The formula contains a reference to a field that is not in the table": This error occurs when the formula references a field that is not in the table. To fix this, make sure that the field is included in the table and that the formula is referencing the correct field.
Error: "The formula contains a reference to a field that is not in the correct format": This error occurs when the formula references a field that is not in the correct format. To fix this, make sure that the field is in the correct format and that the formula is referencing the correct field.

Conclusion

In conclusion, creating a formula for a calculated column that checks if a time is between two other times, including minutes hacked, is a straightforward process. By using the calculated column formula, you can easily check if a submitted time falls within a specific range, making it easier to manage and analyze data in your Power Apps form.

===========================================================

Introduction

In our previous article, we explored how to create a formula for a calculated column that checks if a time is between two other times, including minutes hacked. In this article, we'll answer some frequently asked questions (FAQs) related to this topic.

Q&A

Q: What is the difference between the TIMEVALUE function and the HOUR and MINUTE functions?

A: The TIMEVALUE function is used to convert a date and time string into a time value that can be compared. The HOUR and MINUTE functions are used to extract the hour and minute values from a time value, respectively.

Q: Can I use the calculated column formula to check if a time is before or after a specific time?

A: Yes, you can use the calculated column formula to check if a time is before or after a specific time. Simply replace the ">= 9" and "<= 17" conditions with "< 9" and "> 17" conditions, respectively.

Q: How do I adjust the formula to include a specific date range?

A: To adjust the formula to include a specific date range, replace the "9:00 AM" and "5:00 PM" values with the start and end dates of the range. For example, if you want to check if a time is between 2022-01-01 9:00 AM and 2022-01-01 5:00 PM, replace the values with "2022-01-01 9:00 AM" and "2022-01-01 5:00 PM".

Q: Can I use the calculated column formula to check if a time is within a specific range on a specific day of the week?

A: Yes, you can use the calculated column formula to check if a time is within a specific range on a specific day of the week. Simply add a condition to check if the day of the week is the specific day you want to check. For example, if you want to check if a time is between 9:00 AM and 5:00 PM on Monday, add the condition "WEEKDAY(SubmissionDate) = 2".

Q: How do I troubleshoot errors in the calculated column formula?

A: To troubleshoot errors in the calculated column formula, follow these steps:

Check the formula for syntax errors.
Check the data type of the fields referenced in the formula.
Check if the fields referenced in the formula are in the correct format.
Check if the formula is referencing the correct field.

Q: Can I use the calculated column formula to check if a time is within a specific range on a specific date?

A: Yes, you can use the calculated column formula to check if a time is within a specific range on a specific date. Simply add a condition to check if the date is the specific date you want to check. For example, if you want to check if a time is between 9:00 AM and 5:00 PM on 2022-01-01, add the condition "DATE(SubmissionDate) = DATE(2022, 1, 1)".

Conclusion

In conclusion, the calculated column formula is a powerful tool that can be used to check if a time is between two other times, including minutes hacked. By answering some frequently asked questions related to this topic, we've provided you with a better understanding of how to use the calculated column formula and troubleshoot errors.

Additional Resources

Here are some additional resources that you may find helpful:

Power Apps documentation: This is the official documentation for Power Apps, which includes information on how to use the calculated column formula.
Power Apps community forum: This is a community forum where you can ask questions and get help from other Power Apps users.
Power Apps YouTube channel: This is the official YouTube channel for Power Apps, which includes tutorials and other helpful videos.