Solved

Projecting actual and scheduled hours

Posted on 2012-03-10
6
359 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
Comment Utility
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
Comment Utility
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
Comment Utility
Curt it seems that if I replace now() with today() my formula works
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 18

Accepted Solution

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

Curt
0
 
LVL 18

Expert Comment

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

Curt
0
 

Author Comment

by:rsen1
Comment Utility
Thank you I did not make that connection
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now