Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Sheet1!:
F2 = "Date Received" G2 = "Date Completed" Sheet2!: C1 = "Report Date" (variable dates) A3 = "Date Received" linked from Sheet1!F2 M2 = 'Days Late" (currently =DATEDIF(A3,$c$1,"d") However, this set up doesn't eliminate the Sheet1!"Dates Completed" and will count *all* the files and give the number of days in M2"Days Late" from "Date Received" to "Report Date." What I want the report to do is only look at files received and still open (so we can flag them to alert us of slow actions). Darn. And I was so happy I figured out the DATEDIF forumla :-) Any suggestions? And, yes. I double-checked all the cell numbers so no more fears about mislabelling suggested formulas! WoO wOo. Many thanks for any help. B. |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi
I wonder whether you need to pull the data to another sheet. In a spare column on Sheet1 (say column Q) enter in Q1 Days Late In R1 enter your report Date required =IF(G2="",$R$1-F2,"") Format the cell FormatCellsNumberGeneral Copy down column Q as far as required. Highlight row 1, DataFilterAutofilter Use the dropdown on column Q and select Non Blank All late items will show P.S. Glad you worked out Datedif, but in this case just taking the earlier date away from the later date will give you the answer required. -- Regards Roger Govier "MS Questionnairess" wrote in message ... Sheet1!: F2 = "Date Received" G2 = "Date Completed" Sheet2!: C1 = "Report Date" (variable dates) A3 = "Date Received" linked from Sheet1!F2 M2 = 'Days Late" (currently =DATEDIF(A3,$c$1,"d") However, this set up doesn't eliminate the Sheet1!"Dates Completed" and will count *all* the files and give the number of days in M2"Days Late" from "Date Received" to "Report Date." What I want the report to do is only look at files received and still open (so we can flag them to alert us of slow actions). Darn. And I was so happy I figured out the DATEDIF forumla :-) Any suggestions? And, yes. I double-checked all the cell numbers so no more fears about mislabelling suggested formulas! WoO wOo. Many thanks for any help. B. |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Sorry Roger. I don't understand.
F1=label for "Date Recieved" F2="Date Received" data G1=label for "Date Completed" G2="Date Completed" data Q1= label for "Days Late" Q2=???. I assume it's simply (G2-F2) will give the total amount of days betwen "Date Received" and "Date Completed", blanks show up as '38952' R1="Report Date" label - but there's no actual date R2=(G2"",$R$1-F2,"") - but since R1 is simply a label and not an actual date, this doesn't work. I even tried moving the "Report Date" label to S1 and inserted a date in R1 to test the formula and I still get an error. Can we fine-tune this a bit :-) Many thanks for the help "Roger Govier" wrote: Hi I wonder whether you need to pull the data to another sheet. In a spare column on Sheet1 (say column Q) enter in Q1 Days Late In R1 enter your report Date required =IF(G2="",$R$1-F2,"") Format the cell FormatCellsNumberGeneral Copy down column Q as far as required. Highlight row 1, DataFilterAutofilter Use the dropdown on column Q and select Non Blank All late items will show P.S. Glad you worked out Datedif, but in this case just taking the earlier date away from the later date will give you the answer required. -- Regards Roger Govier "MS Questionnairess" wrote in message ... Sheet1!: F2 = "Date Received" G2 = "Date Completed" Sheet2!: C1 = "Report Date" (variable dates) A3 = "Date Received" linked from Sheet1!F2 M2 = 'Days Late" (currently =DATEDIF(A3,$c$1,"d") However, this set up doesn't eliminate the Sheet1!"Dates Completed" and will count *all* the files and give the number of days in M2"Days Late" from "Date Received" to "Report Date." What I want the report to do is only look at files received and still open (so we can flag them to alert us of slow actions). Darn. And I was so happy I figured out the DATEDIF forumla :-) Any suggestions? And, yes. I double-checked all the cell numbers so no more fears about mislabelling suggested formulas! WoO wOo. Many thanks for any help. B. |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi
I meant R1 to be a Date e.g 25/01/2007, the date you want for your Report Q2 should be My mistake it should read =IF(G2="",$R$1-F2,"") In other words, if there is no date in Date completed, calculate the time from Date received until Report date, otherwise enter Null (it has been completed) Column Q is the column to filter on. -- Regards Roger Govier "MS Questionnairess" wrote in message ... Sorry Roger. I don't understand. F1=label for "Date Recieved" F2="Date Received" data G1=label for "Date Completed" G2="Date Completed" data Q1= label for "Days Late" Q2=???. I assume it's simply (G2-F2) will give the total amount of days betwen "Date Received" and "Date Completed", blanks show up as '38952' R1="Report Date" label - but there's no actual date R2=(G2"",$R$1-F2,"") - but since R1 is simply a label and not an actual date, this doesn't work. I even tried moving the "Report Date" label to S1 and inserted a date in R1 to test the formula and I still get an error. Can we fine-tune this a bit :-) Many thanks for the help "Roger Govier" wrote: Hi I wonder whether you need to pull the data to another sheet. In a spare column on Sheet1 (say column Q) enter in Q1 Days Late In R1 enter your report Date required =IF(G2="",$R$1-F2,"") Format the cell FormatCellsNumberGeneral Copy down column Q as far as required. Highlight row 1, DataFilterAutofilter Use the dropdown on column Q and select Non Blank All late items will show P.S. Glad you worked out Datedif, but in this case just taking the earlier date away from the later date will give you the answer required. -- Regards Roger Govier "MS Questionnairess" wrote in message ... Sheet1!: F2 = "Date Received" G2 = "Date Completed" Sheet2!: C1 = "Report Date" (variable dates) A3 = "Date Received" linked from Sheet1!F2 M2 = 'Days Late" (currently =DATEDIF(A3,$c$1,"d") However, this set up doesn't eliminate the Sheet1!"Dates Completed" and will count *all* the files and give the number of days in M2"Days Late" from "Date Received" to "Report Date." What I want the report to do is only look at files received and still open (so we can flag them to alert us of slow actions). Darn. And I was so happy I figured out the DATEDIF forumla :-) Any suggestions? And, yes. I double-checked all the cell numbers so no more fears about mislabelling suggested formulas! WoO wOo. Many thanks for any help. B. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Deleting time part of a Date, subtracting dates | Excel Discussion (Misc queries) | |||
Date format issue | New Users to Excel | |||
Pre-1900 dates | Excel Discussion (Misc queries) | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Using a col of Dates by day I want to determine a wk ending date. | Excel Worksheet Functions |