Help needed creating some new Excel Formulas!

[Natasha88]

I want to use formulas to update a excel sheet based on information pulled in from a webpage. I have a webpage of users who sign into 2 different buildings. The webpage states their name, time they signed in at and if they left before one hour. This information is pulled into Excel (Sample Sheet 2 below).


On another sheet (Sample Sheet 1 below) I would like to record the total number of people who signed in each day, the total number that left before one hour and the number of first time sign in's each month.


Anyone able to guide me as to how I could go about implementing this?

Sample of the data on Sheet 1:

	04/06/2015	05/06/2015
Total No. to Sign In Per Day
Members who left before 1 hour
First Time to Sign In this Month

<tbody>
</tbody>

Sample of the data on Sheet 2:

Name	Location	Date
Barry	Main Building	05/06/2015 12:53:00
Helen	Main Building (less than 1 hour)	05/06/2015 11:30:00
Dave	Main Building	05/06/2015 11:29:00
Brian	Main Building	05/06/2015 11:27:00
Ann	Main Building	05/06/2015 11:24:00
Mary	Main Building	05/06/2015 11:24:00
Joan	Main Building	05/06/2015 11:24:00
Mike	Store	05/06/2015 11:23:00
John	Store (less than 1 hour)	05/06/2015 08:15:00
Kyle	Main Building	04/06/2015 18:32:00
Richard	Main Building (less than 1 hour)	04/06/2015 18:32:00
Derek	Main Building (less than 1 hour)	04/06/2015 18:20:00

<tbody>
</tbody>

I hope my post makes sense! Please let me know if more information is needed

 

[Natasha88]

Anyone able to help? Thanks!

 

[Stridhan]

Are all dates formatted as dates?

 

[Natasha88]

Are all dates formatted as dates?

Sorry I'm not exactly sure if I understand your question but dates are formatted exactly as they are in the above table, "DD/MM/YYYY HH/MM/SS"

 

[Stridhan]

Im no good with dates but this worked for me.

Total No. to Sign In Per Day, in B2 and drag to right

=SUMPRODUCT(--(DATE(YEAR(Sheet2!$C$2:$C$13),MONTH(Sheet2!$C$2:$C$13),DAY(Sheet2!$C$2:$C$13))=B$1))

Members who left before 1 hour, in B3 and copy to right

=SUMPRODUCT(--(DATE(YEAR(Sheet2!$C$2:$C$13),MONTH(Sheet2!$C$2:$C$13),DAY(Sheet2!$C$2:$C$13))=B$1)*--(ISNUMBER(SEARCH("less",Sheet2!$B$2:$B$13))))

First Time to Sign In this Month enter with ctrl+shift+enter,in B4 and copy to right

=MIN(IF(DATE(YEAR(Sheet2!$C$2:$C$13),MONTH(Sheet2!$C$2:$C$13),DAY(Sheet2!$C$2:$C$13))=B$1,Sheet2!$C$2:$C$13))

 

[Natasha88]

Thanks Stridhan, first 2 formulas are working perfectly for me! However I am having a slight issue with the 3rd formula. I added the following to Excel: =MIN(IF(DATE(YEAR(Sheet2!$C$2:$C$13),MONTH(Sheet2!$C$2:$C$13),DAY(Sheet2!$C$2:$C$13))=B$1,Sheet2!$C$2:$C$13)) and used ctrl+shift+enter which added {} to the start and end of the formula. However this is the result I am seeing for "First Time to Sign In this Month" using the sample data I provided in the OP:

	04/06/2015	05/06/2015
Total No. to Sign In Per Day	3	9
Members who left before 1 hour	2	2
First Time to Sign In this Month	42159.76389	42160.34375

<tbody>
</tbody>

 

[Stridhan]

The number you see is dates and time formatted as general, format as dates and you get the "real date".
However, lost in translation, that formula gives you first registration that day(looking at the date in the header) and not that month.

How will you specify this month, is there only one month in the dates from sheet1?

 

[Stridhan]

to get min for headers month then below, with ctrl+shift+enter and copy to right

=MIN(IF(MONTH(Sheet2!$C$2:$C$13)=MONTH(B$1),Sheet2!$C$2:$C$13))

works?

 

[Natasha88]

So formatted as dates I would get the a result in this cell which is a date? Am I understanding this correctly? Actually what I want to display here is a count. So if on a certain day 10 people entered the building and 4 of them were entering for the first time that month, it would display "4".

As for specifying the month, I will be using a new workbook for each month.

 

[Stridhan]

ok, above would only give you first sign in that month, not the count of people who havent signed in earlier.
can have a look at it later or if someone else gets there before me.