Need help with a formula

Platform discussions Special workflows & use cases
1

Hi,

I hope all is well and I was wondering if you anyone can help me with a formula.

On my Monday.com board I currently have three columns:

Hrs Completed So Far
Scheduled Hrs to Date
Owed Hrs

Here are three examples of the data that would be in this fields:

Example 1:
Hrs Completed So Far: 161 Hours
Scheduled Hrs to Date: 135 Hours
Owed Hrs: -26 Hours

Example 2:
Hrs Completed So Far: 64 Hours
Scheduled Hrs to Date: 72 Hours
Owed Hrs: 8 Hours

Example 3:
Hrs Completed So Far: 72 Hours
Scheduled Hrs to Date: 72 Hours
Owed Hrs: 0 Hours

In the three examples listed above I have three project which is under, over or exact in hours.

The Owed Hrs column is a formula which has the following formula:

{Scheduled Hrs to Date}-{Hrs Completed So Far}

I am looking to refine this formula and In need of some help. Ideally, I would like the formula to output the following scenario:

If the Hrs Completed So Far is over the Scheduled Hrs to Date I would like it to simply report back as “Not owed any hours” rather than the negative number (as shown in example #1). However, if the hours are exact or under then to simply report the hours (as shown in example #2 and #3).

I did attempt the formula myself but I am receiving an incorrect formula notice:

IF(
{Hrs Completed So Far}>{Scheduled Hrs to Date},
“Not owed any hours”,
IF(
{Hrs Completed So Far}=<{Scheduled Hrs to Date},
{Scheduled Hrs to Date}-{Hrs Completed So Far}
)
)

Your help will be greatly appreciated.

Thanks

2

Hi @anthonywoods,

Try:

IF({Hrs Completed So Far}>{Scheduled Hrs to Date},"Not owed any hours",
{Scheduled Hrs to Date}-{Hrs Completed So Far})
1 Like
3

Hi @GCavin,

Thank you so much for your input and help.

Your formula tweak helped to resolve my problem.

All the best!

4

Hi @GCavin,

I hope all is well and I was wondering if you could help me tweak the formula.

The part that says “Not owed hours”. I would like to amend this to say “Client has gone over by [ X ] Hours”… The part that says [ X ] would be the hours counted if the “Hrs Completed So Far” goes over the “Scheduled Hrs to Date”.

Again, example 1 would be scenario. I would like to tweak the existing formula and would really appreciate any help you can offer.

Thanks

5

Hi Anthony,

Try

IF({Hrs Completed So Far}>{Scheduled Hrs to Date},"Not owed any hours",
CONCATENATE("Client has gone over by ",{Scheduled Hrs to Date}-{Hrs Completed So Far}," Hours")

 
 

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

6

Hi @GCavin,

Thank you, it didn’t have the desired effect which I wanted from copying and pasting your formula, however, I was able to tweak it based on your recommendations and I was able to get it working with the following:

IF({Hrs Completed in 2024}>={Scheduled Hrs to Date},CONCATENATE("Client has gone over by ",{Scheduled Hrs to Date}-{Hrs Completed in 2024}," Hours"),
{Scheduled Hrs to Date}-{Hrs Completed in 2024})

Thanks for all your help again :slight_smile:

1 Like