Help needed creating some new Excel Formulas!

Natasha88

New Member
Joined
Nov 7, 2014
Messages
9
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/201505/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:
NameLocationDate
BarryMain Building05/06/2015 12:53:00
HelenMain Building (less than 1 hour)05/06/2015 11:30:00
DaveMain Building05/06/2015 11:29:00
BrianMain Building05/06/2015 11:27:00
AnnMain Building05/06/2015 11:24:00
MaryMain Building05/06/2015 11:24:00
JoanMain Building05/06/2015 11:24:00
MikeStore05/06/2015 11:23:00
JohnStore (less than 1 hour)05/06/2015 08:15:00
KyleMain Building04/06/2015 18:32:00
RichardMain Building (less than 1 hour)04/06/2015 18:32:00
DerekMain 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 :)
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Stridhan

Well-known Member
Joined
Mar 5, 2014
Messages
568

ADVERTISEMENT

Im no good with dates but this worked for me.

Total No. to Sign In Per Day, in B2 and drag to right
Code:
=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
Code:
=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
Code:
=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

New Member
Joined
Nov 7, 2014
Messages
9
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/201505/06/2015
Total No. to Sign In Per Day39
Members who left before 1 hour22
First Time to Sign In this Month42159.7638942160.34375

<tbody>
</tbody>
 

Stridhan

Well-known Member
Joined
Mar 5, 2014
Messages
568

ADVERTISEMENT

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

Well-known Member
Joined
Mar 5, 2014
Messages
568
to get min for headers month then below, with ctrl+shift+enter and copy to right
Code:
=MIN(IF(MONTH(Sheet2!$C$2:$C$13)=MONTH(B$1),Sheet2!$C$2:$C$13))

works?
 

Natasha88

New Member
Joined
Nov 7, 2014
Messages
9
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

Well-known Member
Joined
Mar 5, 2014
Messages
568
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top