dates and sum

  1. R

    Help with Lookup

    Column A has a full year of dates. Column L has the number I need. I need to find the last populated date for each month and pull the value from column L. (Some may end on the 20th or some the 31st....etc) I was using =lookup but it doesn't seem to be getting there for me.
  2. M

    Automating Dates

    My "Days Worked" column keeps breaking with every new month... and i think i have it coded wrong. My formula is =NETWORKDAYS($C$7,$C$8)*-1-$C$11 C7 is Today / C8 is Start Date (beginning of the month) / C11 is Holidays (which i manually enter due to company picks).. Currently it's showing...
  3. M

    Formula for showing the age range when dates are given

    Hi, I'm trying to create a simple table that counts dates that fall into a specific age range: <tbody> 0-10 days old ? 10-20 days old ? Over 20 days old ? </tbody> The easiest way would be to add a column to where the dates are and insert the networkdays formula but I need to avoid and...
  4. V

    Can't figure out dates - SUMIFS or SUMPRODUCT?

    Hello, I have an issue in getting excel to sum a range beginning at Jan-17 up to and including the current month that will auto update ongoing e.g. next month it will include Jan17-Mar17. I can only seem to have it sum just Jan-17 is it best to use sumifs or sumproduct? Will the formatting of...
  5. G

    Calculated Pivot Field Woes...

    Strange issue I'm hoping someone can help me out on: I have a tall data set that has an amount tied to a person and a date, say like such (as a simplified version - the actual data has several thousands of lines for individual people stretching back 90 days): <style type="text/css">...
  6. T

    Help needed in creating a purchase ledger???

    Hi All, I hoping someone may be able to help. I'm creating a purchase ledger and would like to include at the top all a total of: Total Unpaid Payments that are now overdue Payments that fall within 30 days Payments that fall within 14 days Payments that fall within 7 days Below I have tried...
  7. L

    Break down date ranges

    Hello, From the below I have in cell A1:A31 dates. B1:B31 DOW and C1:C31 number of rooms sold. I am wanting a formula that firstly breaks the month down into weeks that run Fri-Thurs and then calculates the number of rooms sold during that week. This will be done every month and so the DOW...
  8. V

    Calculate Biggest Profit & Loss Day

    So I have a range of bets that I would like to summarize by having a cell display the most amount of money won and another showing money loss on a single day and for it to constantly calculate the biggest amount. What would be the best approach for this? The data is formatted like below...
  9. B

    Auto Fill Dates

    I have different dates in column A, names in column B and a product I column C. Is there a formula that will add 7 days to the most recent date for a given name that next time that name is entered if a certain product was entered? Column A Column B Column C 1/1/2015 John...
  10. S

    using SUMIF for date ranges (with blanks)

    Hello, I have some data that I want to add up cumulatively but it has a condition on its date of operation in other columns for instance; <colgroup><col span="4"></colgroup><tbody> A B C D 1 1234 1990 2 673 1990 1997 3 2134 1991 4 7878 1992 1996 5 0 1992 6 567 1994 1999 7...
  11. S

    countif and sumif with dates

    I've got a few Excel sheets with data that looks like this: Apr-2012 1 Apr-2012 1 2 Apr-2012 .34 3 4 Jun-2012 .8 5 Nov-2012 .2 Jan-2013 1 etc. What this data means is this: The first date (the...
  12. A

    Sum of working hours between two "sliced" dates

    Hi again Since my last question yielded great help, I'll try again with another one: I have a table containing employee numbers and their respective number of contractual working hours. Furthermore, the table contains a start date and end date showing when the given number of contractual...
  13. B

    Date forecasting with formatting

    Today is 14-Nov-12 Hello Forum, appreciate some help with this headache ;-) The office has a large spreadsheet where Column A has a "start date" and Column B "Clearance Date" We would like to see a red font for those dates that have passed the current date. A yellow cell for dates that are...
  14. N

    Please i need help

    <tbody> A B C Here is what i need 1 Date # of hours Start Date____i want to enter any day 2 1/1/12 0 # of hours_____enter any number of hours 3 1/2/12 6 End Date______need a return date 4 1/3/12 6 so if i enter 1/2/12 as enter date 5 1/4/12 6 and 24 hours 6 1/5/12 0 i need the end...
  15. S

    Subtotal Ifs

    I have tried the multiple solutions on this forum and can not get it to work. Use-Case: I want to sum a total amount of cells on the IF statement that they are considered a "Renewal" and that they are within the dates of 2/1-2/28/2012. So 2 if clauses, or 3 if I have to specify the 2/1/2012...
  16. D

    sum of time: pivot table

    I am trying to find out the exact sum of time down to the hh:mm:ss on my pivot table from different people on different dates (in half hour increments). Hence: Columns: name name name name name Rows: 01-01-2010 12:00 01-01-2010 12:30 I need a sum of time from each column WITHOUT the dates...
  17. C

    SUMIF - two sets of data, want to sum only one

    Hi all, I got a problem, some financial data I have to sum. There are two sets of data; Forecasts and Actuals; i.e. Row: A1: FY2011 (Forecast) B1: FY2011 (Actual) ...... this goes on for a couple of years. Row A20: Net profitFY2011(Forecast), B20: Net profitFY2011(Actual) ... this goes...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top