Solved

Projecting actual and scheduled hours

Posted on 2012-03-10
6
362 Views
Last Modified: 2012-03-10
Hi Guys,
Please see the attached file. I am trying to project actual hours for a week.

It seems the formula, IF(NOW()>E3,E7,E6)+IF(NOW()>F3,F7,F6)+IF(NOW()>G3,G7,G6)+H6
should work but it doesn't.

Thank you
Robert
schedule-projections.xlsx
0
Comment
Question by:rsen1
  • 3
  • 3
6 Comments
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 37705779
Note that Now() for today will always be greater than the date of today.

eg. 3/10/12 has the value = 40978 and at 10 AM Now() = 40978.42

This means that
=IF(NOW()>E3,E6,E5)+IF(NOW()>F3,F6,F5)+IF(NOW()<G3,G6,G5)+H5
will give this result
=E6+F6+G6+H5
and not
=E6+F6+G5+H5
that you expected as soon as 3/10/12 has started

Cheers,
Curt
0
 

Author Comment

by:rsen1
ID: 37705825
Thank you Curt for your response, what do you see as the correct formula to give my desired result
Robert
0
 

Author Comment

by:rsen1
ID: 37705859
Curt it seems that if I replace now() with today() my formula works
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 18

Accepted Solution

by:
Curt Lindstrom earned 500 total points
ID: 37705874
Yes, that's correct. Today() for 3/10/12 = 40978

Curt
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 37705876
The problem with Now() is that it includes Hours, Minutes and Seconds.

Curt
0
 

Author Comment

by:rsen1
ID: 37705883
Thank you I did not make that connection
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question