Formulas for Timeliness

[rivdan (Dan)]

Hi All
Hope this is helpfull
Here are some formulas i have created to compare 2 date & Times to establish on time, late or early

NOTE IN THE FORMULAS BELOW THE " SHOULD BE THE STRAIGHT " NOT THE ONES AT AN ANGLE

To get timeliness based off 2 times
Note change the Actual Time & Agreed Time for the name of your columns

IF({ACTUAL TIME}={AGREED TIME},“On time”,IF({ACTUAL TIME}>{AGREED TIME},"Late By Hrs/Mins "&HOURS_DIFF({ACTUAL TIME},{AGREED TIME}),"Early By Hrs/Mins "&HOURS_DIFF({ACTUAL TIME},{AGREED TIME})))

Using dates & Times to calculate Days or Hours, Late, Early or On time
This takes out weekends

IF({ACTUAL DATE}="",“No Data”,IF(DAYS({ACTUAL DATE},{AGREED DATE})-(2*(WEEKNUM({ACTUAL DATE})-WEEKNUM({AGREED DATE})))>0,“Late by - “&ABS(DAYS({ACTUAL DATE},{AGREED DATE})-(2*(WEEKNUM({ACTUAL DATE})-WEEKNUM({AGREED DATE}))))&” Working Day(s)”,IF(DAYS({ACTUAL DATE},{AGREED DATE})-(2*(WEEKNUM({ACTUAL DATE})-WEEKNUM({AGREED DATE})))<0,“Early by - “&ABS(DAYS({ACTUAL DATE},{AGREED DATE})-(2*(WEEKNUM({ACTUAL DATE})-WEEKNUM({AGREED DATE}))))&” Working Day(s)”,IF({ACTUAL TIME}={AGREED TIME},“On time”,IF({ACTUAL TIME}>{AGREED TIME},"Late By Hrs/Mins "&HOURS_DIFF({ACTUAL TIME},{AGREED TIME}),"Early By Hrs/Mins "&HOURS_DIFF({ACTUAL TIME},{AGREED TIME}))))))

Using Dates & Times but categorised the times in to 3 Late on the day, Early on the day & On time, Also allowing 1 hour buffer from the agreed time

IF({ACTUAL DATE}="",“No Data”,IF(DAYS({ACTUAL DATE},{AGREED DATE})-(2*(WEEKNUM({ACTUAL DATE})-WEEKNUM({AGREED DATE})))>0,“Late by - “&ABS(DAYS({ACTUAL DATE},{AGREED DATE})-(2*(WEEKNUM({ACTUAL DATE})-WEEKNUM({AGREED DATE}))))&” Working Day(s)”,IF(DAYS({ACTUAL DATE},{AGREED DATE})-(2*(WEEKNUM({ACTUAL DATE})-WEEKNUM({AGREED DATE})))<0,“Early by - “&ABS(DAYS({ACTUAL DATE},{AGREED DATE})-(2*(WEEKNUM({ACTUAL DATE})-WEEKNUM({AGREED DATE}))))&” Working Day(s)”,IF({ACTUAL TIME}<CONCATENATE(SUM(LEFT({AGREED TIME},2))+1) & “:”&RIGHT({AGREED TIME},2),“Early on the day”,IF({ACTUAL TIME}>CONCATENATE(SUM(LEFT({AGREED TIME},2))+1) & “:”&RIGHT({AGREED TIME},2),“Late on the day”,“On Time”)))))

Hope this helps
Cheers Dan

[Julia-monday.com (Julia Fagelman)]

Hi @rivdan

These are amazing formulas!! Thank you so much for sharing them here.

We’d love to see any other nifty tricks you have with the formula column or what you use monday.com for in general?

Cheers,
Julia

[BenP-monday.com (Ben Purzak)]

@rivdan

As of lover of all things formulas, this is incredible!

I’d love to see what else you got or how you’re implementing them in your workflow!

Best,
Ben

[OmerZ (Omer)]

Fantastic, thank you for sharing.

[pamanzanilla (Pam Manzanilla)]

I want to hug you! Thank you for sharing this