Solved

# explain this formula to me   =CEILING(NOW()+419/24,7)+5/24

Posted on 2011-04-21
Medium Priority
255 Views
I am having problems trying to figure out this macro.
=CEILING(NOW()+419/24,7)+5/24

I want the first time it is 5 am, saturday that is at least  396 hours from now
I also want the first time it is 5 am,Sunday  that is at least  415  hours from now
0
Question by:TIMFOX123
• 5
• 3
• 2

LVL 19

Expert Comment

ID: 35440756
now() gives us the current date and time (number of days from the null-date)
first you add 419 hours (17 days and 11 hours).
then you use the ceiling to round it off to multiples of 7 (that is: you search for the start of the next week)
0

LVL 19

Expert Comment

ID: 35440805
this macro will give you the first saturday @ 5am with an offset of 17 days and 11 hours.

for the first saturday @ 5am, use :
``````=CEILING(now(),7) + 5/24
``````
for the first sunday @ 5 am, use :
``````=CEILING(now(),7) + 29/24
``````
0

LVL 19

Expert Comment

ID: 35440827
if you want the number of hours till the next saturday, 5am :

``````=24*(CEILING(now(),7)+5/24 - now())
``````

0

LVL 19

Accepted Solution

Arno Koster earned 2000 total points
ID: 35440859

I want the first time it is 5 am, saturday that is at least  396 hours from now
``````=CEILING(NOW()+396/24,7)+5/24
``````

I also want the first time it is 5 am,Sunday  that is at least  415  hours from now
``````=CEILING(NOW()+415/24,7)+29/24
``````
0

Author Comment

ID: 35440898
round off or round down ?

then you use the ceiling to round it off to multiples of 7

Round off makes 6.9 = 7 and   6.1 = 6
Round down makes 6.9 = 6   and 6.1 = 6

0

LVL 19

Expert Comment

ID: 35441199
the ceiling rounds it up

so 6.9 = 7 and 6.0001 = 7 and 6.0 = 6
0

LVL 34

Expert Comment

ID: 35441443
0

Author Comment

ID: 35442776
robhenson:

When I tried make this work, for myself I could not get it to work .

I just did not work as flawlessly :)

0

Author Closing Comment

ID: 35442789
thank you both
0

LVL 34

Expert Comment

ID: 35442894
You should have posted a comment on the original question rather than starting a new one, it would not have cost you any thing - points etc.

Cheers
Rob H
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month8 days, 10 hours left to enroll