• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Show Recent Data

T

The Doctor

Member
I have a number of jobs going at any one time, and I would like a way to see how many hours have been worked on a job in the last week. Is there formula that says to add range of hours worked if date within 7 days? I would need a similar formula for non labor costs, ie invoices received within last 7 days as well. Any ideas would be welcomed.


Thanks
 
Hi, The Doctor!

It all depends on how is your data stored. Please describe in what columns do you have data that identifies date, the amount of hours, which format do those cell have, where do you want to get the summary info, ...

Regards!
 
The date info is in 'Page2'!D37:D44, the labor is 'Page2'!F37:F44 I need the sum of labor done within 7 days to display on 'Page1'!AD10.


Is that the info you need?
 
Hi, The Doctor!


Let's see if I haven't misunderstood:

a) cells D37:D44 in Page2 sheet, have date values (numeric, no matter what day format, but integers)

b) that range has all the possible occurrences of dates for at least the last 7 days

c) cells F37:F44 in Page2 sheet, have time values (numeric, no matter what time format, but as decimal numbers lesser 1)

d) that range has all the possible occurrences of times for at least the last 7 days


If so, you can type in cell AD10 in Page1 sheet:

=SUMAR.SI(Page2!D37:D44;">"&HOY()-7;Page2!F37:F44) -----> in english: =SUMIF(Page2!D37:D44,">"&TODAY()-7,Page2!F37:F44)


Apply same format of F column to AD10 cell.


Regards!
 
Brilliant! I knew it had to be a sumif, I just didn't know how to tell it within 7 days. It works like a dream, thanks.
 
You must log in or register to reply here.
Back
Top