Skip to main content

Hours till end of week formula

  • June 22, 2021
  • 6 replies
  • 43 views
A
  • Anonymous

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!

6 replies

Kamille_Parks11
Forum|alt.badge.img+27

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.

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • June 22, 2021

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.

Kamille_Parks11
Forum|alt.badge.img+27

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.

kuovonne
A
  • Anonymous
  • June 24, 2021

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

E
  • Eric11
  • New Participant
  • June 24, 2021

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?

E
  • Eric11
  • New Participant
  • June 24, 2021

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’)

Terms & ConditionsAccessibility statement