We help IT Professionals succeed at work.

in excell how do I say "now + 115 hours, then 5 am on the fist saturday after ?

TIMFOX123
TIMFOX123 asked
on
Medium Priority
209 Views
Last Modified: 2012-05-11
I want to find the first saturday @ 5 am  more than 115 hours from now.

I can perform a task at 5 am on any saturday  however I need to let the owner know 115 hours ahead of time.

this is a 1 time deal so I want to use the first satuday at 5 am that fits the requirement.  
Comment
Watch Question

Finance Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
To explain:

=NOW() gives time at point of calculation

Adding 115/24 will add sufficient days and hours.

=CEILING(###,n) will round up the ### to the next factor of n. As dates are held as a serial number incremented from 01/01/1900 which happened to be a Sunday. The whole number being the date and the decimal portion of a number being the time eg 3am being 0.25 and midday being 0.5 etc

Therefore counting up in units of 7 will give the whole serial number dates for Saturdays. Then adding 5/24 to the result will be the decimal part of the number for 5am.

Regards
Rob H

Author

Commented:
I am totally impressed

I would have taken tooooo long to figure this one out

thx
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.