MS Excel Formula

[matrixx]

Hi,

 

I am creating a Staff Sign in sheet in Excel 2007. I have a monthly sheet on each page of the workbook. The first few rows consist of the individual dates. I am currently entering the time each staff member arrives by using

(CTRL + SHIFT + 

which inserts the exact time in each appropriate cell. What I am stuck with is I would like a percentage at the end of each week or month amount of lates. For e.g. if the start time is 07:00 and a staff member arrives at 07:02 I would like the spreadsheet to calculate it as a late.

 

Is this possible?

 

Thanks in advance.

Edited September 22, 2013 by plexer

[CESIL]

If the staff member has some sort of id on the sheet then using a lookup table with members and their respective start times should do what you want.

 

If you post a sample file I am sure some kind soul will take a closer look

[matrixx]

Hi,

 

I have posted a sample file, I would like the file to recognise staff signing in after 08:30 to be considered late. A percentage of the weekly lates will also be of great help.

Staff Sign In Sheet.xls

[steve]

I'd use conditional formatting to highlight lates, add a column for the start time and use the following to calculate the percentage lates:

 

=COUNTIF(E7:I7,">"&C7)/COUNT(E7:I7)*100

 

I've put this in the file, but the fancy month choice bit in the top corner breaks it.

 

Staff Sign In Sheet.xls

[elsiegee40]

This sounds like a little job for Google forms which can timestamp the entry and put the results in a spreadsheet.

[SYNACK]

This sounds like a little job for Google forms which can timestamp the entry and put the results in a spreadsheet.

 

Or Access, or InfoPath, or SharePoint/o365 2013 excel data collection forms

[plexer]

Are you including sat/sun or not?

 

Conditional formatting should do the highlighting although I've put a formula in if minute(cell_ref)>30 but it doesn't work with the ctrl shift ; keypress for some reason only if you type it in.

 

Ben