Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default report date - date rec.ved=days late, but how to rid dates complet

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default report date - date rec.ved=days late, but how to rid dates complet

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default report date - date rec.ved=days late, but how to rid dates com

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default report date - date rec.ved=days late, but how to rid dates com

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting time part of a Date, subtracting dates Shirley Munro Excel Discussion (Misc queries) 3 June 21st 06 11:58 AM
Date format issue CindyLF1 New Users to Excel 3 June 12th 06 06:18 PM
Pre-1900 dates Richard Gadsden Excel Discussion (Misc queries) 1 March 26th 06 01:46 AM
NETWORKDAYS - Multiple Date Selection Annabelle Excel Discussion (Misc queries) 3 October 4th 05 07:04 PM
Using a col of Dates by day I want to determine a wk ending date. Ken Espo Excel Worksheet Functions 1 February 3rd 05 09:09 PM


All times are GMT +1. The time now is 07:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"