Help

Hours till end of week formula

Topic Labels: Formulas
2059 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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 6

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.

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.

Anonymous
Not applicable

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

Eric
4 - Data Explorer
4 - Data Explorer

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