This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Working on a payroll spreadsheet....I have a cell that, dependant on the previous cell, displays only the first 8 hours worked. Maximum of 8. If a person works 13 hours in a day, this cell will display a maximum of 8. But can also display a lower amount if necessary. I believe MAX and MIN may come into play here. I may also be formatting my cells wrong. Thanks!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
In that context, the 8 is interpreted as 8 days. Thus, 16 hours and 30 minutes is less then 8 days.
To make it work, use =MIN( 8/24, H7 )
Hello, Robert SimsPI
Try putting quotation marks around 8.
For example:
I7=MIN(H7;"8:00")
Best wishes,
Lucia Wu - MSFT | Microsoft Community Support Specialist
Hello!
I'm sorry I haven't responded...been out on vacation.
I tried your recommendation of using the =MIN(8,referenced cell). Here is my result:
I'm not getting the desired result of a MAXIMUM of 8 Hours. Could it be with the formatting of H7 or I7? I have them both set to Custom [H]:mm.
I appreciate your assistance!
Hello, Robert SimsPI
I haven't received the message from you yet. If there is anything more that I can do for you or if anything is unclear, please do not hesitate to let me know.
Best wishes,
Lucia Wu - MSFT | Microsoft Community Support Specialist
Hello, Robert SimsPI
Welcome to Microsoft Community.
I realize that you want to display the first 8 hours a person worked in a cell based on the previous cell. No matter what the previous cell shows in terms of hours worked, this cell will display a maximum of 8 hours. If less than 8 hours, it will display the actual value.
For example, if a person works 13 hours, the cell is 8.
If a person works 6 hours, the cell is 6.
Please feel free to correct me if my understanding is wrong.
You are right, it is available to use the MIN formula.
=MIN(8; reference to the previous cell)
For example, if the previous cell is A1, you can enter the following formula in B1:
=MIN(8;A1)
I hope my reply helps you. Please let me know whether the result fit your expectation. I'm looking forward to your reply.
Best wishes,
Lucia Wu - MSFT | Microsoft Community Support Specialist