Using functions to count date format data

  • Thread starter Thread starter dljudd
  • Start date Start date
D

dljudd

I am trying to work with the data below.
I am loading this data from an SQL Reporting Data Base to a maste
sheet in Excel that I can refresh data on a daily or weekly basis. I a
trying to set up a second sheet to show the total count of record
entered for a certain date using the column WO_TSK_STATUS_DATE. But th
date is formated like 20040622 or yyyymmdd. What kind of formula o
function to count the records from sheet one and put a total for
specified date in a cell on sheet 2 using the existing date format?

You can view this data better by copying it to an excel sheet.

Thanks

FACILITY UNIT WORK_ORDER_NBR WORK_ORDER_TASK EQUIPMENT_NAME EQUIPMENT_NUMBER EQUIPMENT_TYPE WO_TSK_INITIATOR WO_TSK_PLANNER WO_TSK_STATUS_DATE WORK_ORDER_TYPE OP_SYSTEM
MSK CTISSU 00331473 01 LOG DISCHARG
SECTION 21-151 REWNDR RLHOPKIN 20040614 UP R151
MSK CCORE 00331488 01 UNWIN
SECTION 24-012 CORMCH JPUELSMA 20040607 UP 24012
MSK CNAPKN 00331537 01 BRETTING FOLDER S/
4507-91 29-185 FOLDER SREHEARD 20040606 UP G185
MSK CFTWL 00331604 01 APPLETON LO
SAW 33-040 SAW SREHEARD 20040606 UP S063
MSK CNAPKN 00331606 01 ENTIRE MUSKOGEE EMBOSSED QUARTERFOL
DEPARTMENT 29-000 DEPT COTYLER 20040606 UP FLDGEN
MSK CNAPKN 00331672 01 HAYSSEN 5200 POLYWRAPPER FOR TWINSTAC
QUARTERFOLD NAPKINS 29-075 WRAPPR JPUELSMA 20040621 UP B071
MSK CTISSU 00331740 01 LOG DUM
SECTION 21-163 ACCUM RCWALLAC 20040618 UP R16
 
Custom format
00000
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I am trying to work with the data below.
I am loading this data from an SQL Reporting Data Base to a master
sheet in Excel that I can refresh data on a daily or weekly basis. I am
trying to set up a second sheet to show the total count of records
entered for a certain date using the column WO_TSK_STATUS_DATE. But the
date is formated like 20040622 or yyyymmdd. What kind of formula or
function to count the records from sheet one and put a total for a
specified date in a cell on sheet 2 using the existing date format?

You can view this data better by copying it to an excel sheet.

Thanks

FACILITY UNIT WORK_ORDER_NBR WORK_ORDER_TASK EQUIPMENT_NAME EQUIPMENT_NUMBER
EQUIPMENT_TYPE WO_TSK_INITIATOR WO_TSK_PLANNER WO_TSK_STATUS_DATE
WORK_ORDER_TYPE OP_SYSTEM
MSK CTISSU 00331473 01 LOG DISCHARGE
SECTION 21-151 REWNDR RLHOPKIN 20040614 UP R151
MSK CCORE 00331488 01 UNWIND
SECTION 24-012 CORMCH JPUELSMA 20040607 UP 24012
MSK CNAPKN 00331537 01 BRETTING FOLDER S/N
4507-91 29-185 FOLDER SREHEARD 20040606 UP G185
MSK CFTWL 00331604 01 APPLETON LOG
SAW 33-040 SAW SREHEARD 20040606 UP S063
MSK CNAPKN 00331606 01 ENTIRE MUSKOGEE EMBOSSED QUARTERFOLD
DEPARTMENT 29-000 DEPT COTYLER 20040606 UP FLDGEN
MSK CNAPKN 00331672 01 HAYSSEN 5200 POLYWRAPPER FOR TWINSTACK
QUARTERFOLD NAPKINS 29-075 WRAPPR JPUELSMA 20040621 UP B071
MSK CTISSU 00331740 01 LOG DUMP
SECTION 21-163 ACCUM RCWALLAC 20040618 UP R163
 
It's nice to try to post to the correct thread ... Sorry!
Custom format
00000
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I am trying to work with the data below.
I am loading this data from an SQL Reporting Data Base to a master
sheet in Excel that I can refresh data on a daily or weekly basis. I am
trying to set up a second sheet to show the total count of records
entered for a certain date using the column WO_TSK_STATUS_DATE. But the
date is formated like 20040622 or yyyymmdd. What kind of formula or
function to count the records from sheet one and put a total for a
specified date in a cell on sheet 2 using the existing date format?

You can view this data better by copying it to an excel sheet.

Thanks

FACILITY UNIT WORK_ORDER_NBR WORK_ORDER_TASK EQUIPMENT_NAME EQUIPMENT_NUMBER
EQUIPMENT_TYPE WO_TSK_INITIATOR WO_TSK_PLANNER WO_TSK_STATUS_DATE
WORK_ORDER_TYPE OP_SYSTEM
MSK CTISSU 00331473 01 LOG DISCHARGE
SECTION 21-151 REWNDR RLHOPKIN 20040614 UP R151
MSK CCORE 00331488 01 UNWIND
SECTION 24-012 CORMCH JPUELSMA 20040607 UP 24012
MSK CNAPKN 00331537 01 BRETTING FOLDER S/N
4507-91 29-185 FOLDER SREHEARD 20040606 UP G185
MSK CFTWL 00331604 01 APPLETON LOG
SAW 33-040 SAW SREHEARD 20040606 UP S063
MSK CNAPKN 00331606 01 ENTIRE MUSKOGEE EMBOSSED QUARTERFOLD
DEPARTMENT 29-000 DEPT COTYLER 20040606 UP FLDGEN
MSK CNAPKN 00331672 01 HAYSSEN 5200 POLYWRAPPER FOR TWINSTACK
QUARTERFOLD NAPKINS 29-075 WRAPPR JPUELSMA 20040621 UP B071
MSK CTISSU 00331740 01 LOG DUMP
SECTION 21-163 ACCUM RCWALLAC 20040618 UP R163
 
You can use this:

=Countif(E1:E15,20040606)

This will count all the ocurrences for 6/6/2004,

You can convert all your dates to a standard date format
with a helper column using this formula:

=DATE(--LEFT(E2,4),--MID(E2,5,2),--RIGHT(E2,2))

where you have a yyyymmdd format date on E2.

Cheers
Juan
 
dljudd wrote ...
I am trying to work with the data below.
I am loading this data from an SQL Reporting Data Base to a master
sheet in Excel that I can refresh data on a daily or weekly basis. I am
trying to set up a second sheet to show the total count of records
entered for a certain date using the column WO_TSK_STATUS_DATE.
Click to expand...

This is the kind of thing database queries are for!

Set up a second query, based on the first e.g. something like

SELECT
COUNT(*) AS Count_of_Status_Date
FROM MyTable
WHERE
WO_TSK_STATUS_DATE = '20040622'

Jamie.

--
 
Back
Top