Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Need to pull <=14 Days, <=30 Days, 30 Days from a date column
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Ken Ken is offline
external usenet poster
  
Posts: 590
Default Need to pull <=14 Days, <=30 Days, 30 Days from a date column
How do I write a formula to pull the following :

Column "Y" represents a support ticket date. I need to pull out how many
tickets are <=14 Days old, <=30 Days old, 30 Days old

B Y
incident.id open.time
E-SD090250782 02/03/2009 17:20
E-SD090340926 24/03/2009 7:18
E-SD090546927 13/05/2009 15:41
E-SD090588589 25/05/2009 7:33
E-SD090710099 23/06/2009 12:06
N-SD090052310 23/07/2009 16:22
N-SD090054143 28/07/2009 9:39
N-SD090055225 29/07/2009 14:37
N-SD090062940 14/08/2009 9:54
N-SD090064958 19/08/2009 9:35
N-SD090066937 24/08/2009 11:12
N-SD090068949 27/08/2009 10:15
N-SD090068953 27/08/2009 10:19
N-SD090075083 08/09/2009 13:39
N-SD090075111 08/09/2009 14:01
N-SD090077670 11/09/2009 14:52
N-SD090077713 11/09/2009 15:16
N-SD090078247 14/09/2009 8:33
N-SD090079206 15/09/2009 8:13
N-SD090085787 24/09/2009 11:02
N-SD090087506 28/09/2009 8:33
N-SD090087871 28/09/2009 12:06
N-SD090090972 01/10/2009 14:37
N-SD090093022 05/10/2009 15:26
N-SD090095046 07/10/2009 16:40
N-SD090095341 08/10/2009 8:34
N-SD090095457 08/10/2009 9:50
N-SD090102332 19/10/2009 11:23
N-SD090103238 20/10/2009 9:17
N-SD090103355 20/10/2009 10:46
N-SD090104409 21/10/2009 10:36
N-SD090104768 21/10/2009 14:40
N-SD090105382 22/10/2009 9:56
N-SD090105441 22/10/2009 10:55


Appreciate the help

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,696
Default Need to pull <=14 Days, <=30 Days, 30 Days from a date column
I wuld think you'd want to use, say, column Z.

=IF(TODAY()-Y230,"30",IF(TODAY()-Y214,"15-30","<15"))

Then filter and pick each.

"Ken" wrote:

How do I write a formula to pull the following :

Column "Y" represents a support ticket date. I need to pull out how many
tickets are <=14 Days old, <=30 Days old, 30 Days old

B Y
incident.id open.time
E-SD090250782 02/03/2009 17:20
E-SD090340926 24/03/2009 7:18
E-SD090546927 13/05/2009 15:41
E-SD090588589 25/05/2009 7:33
E-SD090710099 23/06/2009 12:06
N-SD090052310 23/07/2009 16:22
N-SD090054143 28/07/2009 9:39
N-SD090055225 29/07/2009 14:37
N-SD090062940 14/08/2009 9:54
N-SD090064958 19/08/2009 9:35
N-SD090066937 24/08/2009 11:12
N-SD090068949 27/08/2009 10:15
N-SD090068953 27/08/2009 10:19
N-SD090075083 08/09/2009 13:39
N-SD090075111 08/09/2009 14:01
N-SD090077670 11/09/2009 14:52
N-SD090077713 11/09/2009 15:16
N-SD090078247 14/09/2009 8:33
N-SD090079206 15/09/2009 8:13
N-SD090085787 24/09/2009 11:02
N-SD090087506 28/09/2009 8:33
N-SD090087871 28/09/2009 12:06
N-SD090090972 01/10/2009 14:37
N-SD090093022 05/10/2009 15:26
N-SD090095046 07/10/2009 16:40
N-SD090095341 08/10/2009 8:34
N-SD090095457 08/10/2009 9:50
N-SD090102332 19/10/2009 11:23
N-SD090103238 20/10/2009 9:17
N-SD090103355 20/10/2009 10:46
N-SD090104409 21/10/2009 10:36
N-SD090104768 21/10/2009 14:40
N-SD090105382 22/10/2009 9:56
N-SD090105441 22/10/2009 10:55


Appreciate the help

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
Ken Ken is offline
external usenet poster
  
Posts: 590
Default Need to pull <=14 Days, <=30 Days, 30 Days from a date column
I can make that work but how do I include today's tickets? For example if I
do the following it will display "False" for the last two entries beause they
have today's date:

=IF(TODAY()-Y2180,"180 Days",IF(TODAY()-Y290,"90
Days",IF(TODAY()-Y230,"30 Days",IF(TODAY()-Y214,"14
Days",IF(TODAY()-Y20,"0-14 Days")))))

Regards,

"Sean Timmons" wrote:

I wuld think you'd want to use, say, column Z.

=IF(TODAY()-Y230,"30",IF(TODAY()-Y214,"15-30","<15"))

Then filter and pick each.

"Ken" wrote:

How do I write a formula to pull the following :

Column "Y" represents a support ticket date. I need to pull out how many
tickets are <=14 Days old, <=30 Days old, 30 Days old

B Y
incident.id open.time
E-SD090250782 02/03/2009 17:20
E-SD090340926 24/03/2009 7:18
E-SD090546927 13/05/2009 15:41
E-SD090588589 25/05/2009 7:33
E-SD090710099 23/06/2009 12:06
N-SD090052310 23/07/2009 16:22
N-SD090054143 28/07/2009 9:39
N-SD090055225 29/07/2009 14:37
N-SD090062940 14/08/2009 9:54
N-SD090064958 19/08/2009 9:35
N-SD090066937 24/08/2009 11:12
N-SD090068949 27/08/2009 10:15
N-SD090068953 27/08/2009 10:19
N-SD090075083 08/09/2009 13:39
N-SD090075111 08/09/2009 14:01
N-SD090077670 11/09/2009 14:52
N-SD090077713 11/09/2009 15:16
N-SD090078247 14/09/2009 8:33
N-SD090079206 15/09/2009 8:13
N-SD090085787 24/09/2009 11:02
N-SD090087506 28/09/2009 8:33
N-SD090087871 28/09/2009 12:06
N-SD090090972 01/10/2009 14:37
N-SD090093022 05/10/2009 15:26
N-SD090095046 07/10/2009 16:40
N-SD090095341 08/10/2009 8:34
N-SD090095457 08/10/2009 9:50
N-SD090102332 19/10/2009 11:23
N-SD090103238 20/10/2009 9:17
N-SD090103355 20/10/2009 10:46
N-SD090104409 21/10/2009 10:36
N-SD090104768 21/10/2009 14:40
N-SD090105382 22/10/2009 9:56
N-SD090105441 22/10/2009 10:55


Appreciate the help

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
Ken Ken is offline
external usenet poster
  
Posts: 590
Default Need to pull <=14 Days, <=30 Days, 30 Days from a date column
Never mind I figured it out. This one worked.

=IF(TODAY()-Z35=180,"180 Days",IF(TODAY()-Z35=90,"90
Days",IF(TODAY()-Z3560,"60 Days",IF(TODAY()-Z35=30,"30
Days",IF(TODAY()-Z35=14,"14 Days",IF(TODAY()-Z35<14,"0-14 Days"))))))

Thanks for your help! Much appreciated.

"Ken" wrote:

I can make that work but how do I include today's tickets? For example if I
do the following it will display "False" for the last two entries beause they
have today's date:

=IF(TODAY()-Y2180,"180 Days",IF(TODAY()-Y290,"90
Days",IF(TODAY()-Y230,"30 Days",IF(TODAY()-Y214,"14
Days",IF(TODAY()-Y20,"0-14 Days")))))

Regards,

"Sean Timmons" wrote:

I wuld think you'd want to use, say, column Z.

=IF(TODAY()-Y230,"30",IF(TODAY()-Y214,"15-30","<15"))

Then filter and pick each.

"Ken" wrote:

How do I write a formula to pull the following :

Column "Y" represents a support ticket date. I need to pull out how many
tickets are <=14 Days old, <=30 Days old, 30 Days old

B Y
incident.id open.time
E-SD090250782 02/03/2009 17:20
E-SD090340926 24/03/2009 7:18
E-SD090546927 13/05/2009 15:41
E-SD090588589 25/05/2009 7:33
E-SD090710099 23/06/2009 12:06
N-SD090052310 23/07/2009 16:22
N-SD090054143 28/07/2009 9:39
N-SD090055225 29/07/2009 14:37
N-SD090062940 14/08/2009 9:54
N-SD090064958 19/08/2009 9:35
N-SD090066937 24/08/2009 11:12
N-SD090068949 27/08/2009 10:15
N-SD090068953 27/08/2009 10:19
N-SD090075083 08/09/2009 13:39
N-SD090075111 08/09/2009 14:01
N-SD090077670 11/09/2009 14:52
N-SD090077713 11/09/2009 15:16
N-SD090078247 14/09/2009 8:33
N-SD090079206 15/09/2009 8:13
N-SD090085787 24/09/2009 11:02
N-SD090087506 28/09/2009 8:33
N-SD090087871 28/09/2009 12:06
N-SD090090972 01/10/2009 14:37
N-SD090093022 05/10/2009 15:26
N-SD090095046 07/10/2009 16:40
N-SD090095341 08/10/2009 8:34
N-SD090095457 08/10/2009 9:50
N-SD090102332 19/10/2009 11:23
N-SD090103238 20/10/2009 9:17
N-SD090103355 20/10/2009 10:46
N-SD090104409 21/10/2009 10:36
N-SD090104768 21/10/2009 14:40
N-SD090105382 22/10/2009 9:56
N-SD090105441 22/10/2009 10:55


Appreciate the help

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Employee days worked (-Holidays, -weekends, Snow Days, etc) Denise Excel Discussion (Misc queries) 2 December 31st 08 04:37 PM
Calc days between two dates and exclude leap year days scoz Excel Worksheet Functions 5 November 23rd 07 03:58 PM
Conditional Formatting Dates calculating 10 days and 30 days from a certain date Sioux[_2_] Excel Worksheet Functions 2 October 11th 07 02:04 PM
COUNT how many ROWS ago out of 10 days that the highest high in 10 days was made rhhince Excel Worksheet Functions 1 January 14th 07 09:56 PM
Convert decimal days to Days,hours, minutes Todd F. Excel Worksheet Functions 3 March 14th 06 03:38 PM


All times are GMT +1. The time now is 08:59 AM.

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"

 

Contact Us ExcelBanter forum home Privacy Statement