Skip to main content

Hi everyone!



I have a work week that operates from Monday-Friday (24 hours).



I am trying to develop a formula that would show the total hours remaining from the current time until the week closes each Friday at 11:59PM. This would have to reset each week.



So far I have



FORMULA:



DATETIME_DIFF(NOW(), {FORMULA NEEDED}, “hours”)



This is a part of a much bigger formula. Thanks for your help!

Sounds like the formula would be:



DATETIME_DIFF(DATEADD(TODAY(), 6 - WEEKDAY(TODAY()), 'days'), NOW(), 'hours')


This will result in negative hours being reported if “today” is Saturday, and assumes weeks start on Sunday.

Sounds like the formula would be:



DATETIME_DIFF(DATEADD(TODAY(), 6 - WEEKDAY(TODAY()), 'days'), NOW(), 'hours')


This will result in negative hours being reported if “today” is Saturday, and assumes weeks start on Sunday.


If the date/time fields are local time instead of GMT, the formula will need to take time zones into account.



It looks like your formula calculates the difference based on a closing time of Saturday, 12:00am GMT, versus Friday at 11:59pm. It probably doesn’t make much of a difference in the calculations, but I thought I’d point it out for anyone trying to understand your formula.

If the date/time fields are local time instead of GMT, the formula will need to take time zones into account.



It looks like your formula calculates the difference based on a closing time of Saturday, 12:00am GMT, versus Friday at 11:59pm. It probably doesn’t make much of a difference in the calculations, but I thought I’d point it out for anyone trying to understand your formula.


Yeah I should have mentioned that. I assumed by “11:59pm” they meant “11:59:59pm”, inclusive, so that the formula retrieves the number of whole hours between “now” and the actual end of Friday, not one whole minute before the end of Friday.

Thanks! This is helpful, I will try it out!!

Hi @Kamille_Parks @kuovonne ,



Thanks for your ideas! I tried adding in the formula and at this very moment, it is 9:10am EST and the actual time till Friday 11:59PM EST is 38 hours, 49 minutes, 59 seconds



The formula is producing “34” hours. I’m assuming this is what you meant by “If the date/time fields are local time instead of GMT, the formula will need to take time zones into account.”



Any idea on how to make it be correct for the EST (New York) timezone?

Hi @Kamille_Parks @kuovonne ,



Thanks for your ideas! I tried adding in the formula and at this very moment, it is 9:10am EST and the actual time till Friday 11:59PM EST is 38 hours, 49 minutes, 59 seconds



The formula is producing “34” hours. I’m assuming this is what you meant by “If the date/time fields are local time instead of GMT, the formula will need to take time zones into account.”



Any idea on how to make it be correct for the EST (New York) timezone?


Actually, this seems to be working:



DATETIME_DIFF(DATEADD(TODAY(), 6 - WEEKDAY(TODAY()), ‘days’), {EST Timezone Delete}, ‘hours’)

Reply


Terms & Conditions