Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Excel Forum

Count For Present & Absent Days

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I use Excel 2000
I have sheet muster for my clients of January, 2005 like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4

1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power


View Answers     

Similar Excel Tutorials

Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
Increment a Date By X Days for Each Row in Excel
How to quickly increment a date by a certain number of days for each entry. Type the first date in a cell. Make sur ...
Get a Date that is So Many Working Days Before or After a Date in Excel - WORKDAY
How to find a date that is so many days before or after a specified date, excluding weekends and any holidays. This ...
Get the Number of Workdays Between Two Dates in Excel
How to calculate the total number of working days between two dates in Excel.  This allows you to exclude all weeke ...
Get Day Name from a Date in Excel
How to get the name of a day from a date in Excel. This returns, for example, "Tuesday" for the date October 4, 201 ...
Count Cells Containing TRUE or FALSE in Excel
Count the number of cells that contain TRUE, FALSE, or one of the two. This lets you count the logical values in a ...

Helpful Excel Macros

Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format

Similar Topics







Hello everyone.

This is marc, i have one doubt in my mind. that was how to create automated attendance sheet with login name. i.e. there are 25 employees working in particular corporate sector. but HR or time keeper doesn't know who are present and absent. so if it is possible to create a login sheet which directly pop up the attendance in another sheet for the current date for particular employee. please help me. Thank you in advance,

...Marc


Hello,

I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like:

01/01/2007
02/01/2007
03/01/2007

I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc.

Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated!

Many thanks,
Caitlin


I'm trying to come up with a solution to calculate the man hours and days worked over multiple sheets.

I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.

The days of the week consists of "Name" "Date" Hours"

The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"

Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.

I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.

I have attached a sample file.

Thanks in advance for your help.


Good afternoon!

I'm working with Excel 2003 sp 3 on Windows xp.

I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker:

=INT(Q3/8)+(((Q3/8)-(INT(Q3/8)))*0.8)

That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example:

weekending 12/25 = 16.2 which is 16 man days and 2 hours
weekending 1/1 = 24.7 which is 24 man days and 7 hours

Total = 41 man days and 1 hour

I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or "pushing a button".

Any advice is greatly appreciated!

Happy New Year!

~ Brenda ~


Very new in this board. If I post this question in the wrong forum please bear with me.
There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year Jan-Dec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from Feb-Dec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.
Thanks and sorry for this long questions


Is there a way to calculate the average days between a column of dates?

Basically, I've got a sheet that contains a table of entries, and each entry gets a date stamp in the first column. I would like to have the sheet display the average time, in days, between entries. Is this possible?

Thank you in advance!
~cp


I am trying to copy balances from one worksheet to another over a total of 12
worsheets ..... this is for petty cash monthly sheets and I need a running
total over the 12 sheets.

At present I have to do this manually .... is there a formula that I can use
that will automatically carry the balances over

Thanks for your help



I have a spreadsheet counting down the days until a date is reached. However, after that date is reached and passed I get a #NUM! error.

What can I do to countdown the days as below AND countdown the days that have passed as a NEGATIVE NUMBER???


=DATEDIF(NOW(),D11,"D")


I was able to convert start date and and end date to the number of days
inbetween the two dates. Does anyone know how to then convert the number of
days into months?



A previous poster explained what I also need; there were no responses,
so I thought I would try again.

Does anyone know of an Excel template--or a small stand-alone
program--that will calculate the interest/principal breakdown when
payments are varied in amount and frequency? Free or low-cost, please.
I need one that will work on my Mac/Office 2004. I will need to print
out periodic reports.

Here is the way the previous poster described it:
"Excel template: Loan Amortization for random/irregular payments,
figures days
between payment dates.
I have a loan with a variable beginning balance and irregular payments
with
annual large payment. (based on collections)
Would like to enter payment and date.
then Excel would figure days since last payment, interest amount,
principal
amount, Ending Principal balance.
And total interest paid, total pricipal paid
If I change the starting principal, excel would recalculate all
entries."

Thanks very much.




I'm trying to format a worksheet to show how many days overdue certain tasks are, and running into a couple of issues. The sheet is set up with Due Dates in Column A, Days Overdue in Column B, and Date Completed in Column C. My first thought was just to use as simple a formula as possible in Column B =Today()-A2. This worked alright for tasks that already had Due Dates assigned, but if Column A was blank I'd get a high number in Column B. I also realized that tasks will continue to show as overdue even after I enter the completed-on date in Column C. Is there a relatively simple way to tackle this formula? Thanks in advance for suggestions. (I've attached an example with the column headings I want to use.)


HI...can anyone help me with a formula for aging data in days (to be aged based on current date vs. for eg., Invoice date)


I have attached a schedule for shift allowance calculation. The conditions for payment are as follows:

3 & 4 shifts = 15% Basic
2 shifts = 10% Basic
If on leave for 21 working days or more, no shift allowance
If on leave for less than 21 working days and on 3 or 4 shifts, shift allowance prorated as (Basic*15%)/21*(diff. btw 21 and no of days leave observed)

If on leave for less than 21 working days and on 2 shifts, shift allowance prorated as (Basic*10%)/21*(diff. btw 21 and no of days leave observed)


The formular I used in the attached worked but the only problem is that I had to sort by no. of shifts and adjust the formular first. I need A formular that does not require sorting. I tried to combine IF, AND, OR formulae but I got error. Please assist me sort this out. Thanks a bunch.


I am using the following formula to calculate the number of days between a date and the current date: =DATEDIF(A1,TODAY(),"d") What do I add to the formula to leave cell blank if no date is entered in the cell for calculation.


I am trying to correct a formula for a productivity worksheet in excel. The idea is that we would have the ability to input the number of hours worked each day and the number of direct hours (working with clients) per day. Then take the daily percentages and add them then devide them to determine the final productivity percentage for the week. Additionally not everyone works a full week so I would need it to only divide the percentages of the days that were completed. Any suggestions? or does anybody know a formula that would accomplish this already? I am good with basic excel and was able to identify a flaw in the current productivity sheet.


Hello, please help me out with this one.

So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!


I automating a task at work that will save some time. Seems simple enough but for some reason the code I have to cause IE to wait unil ready doesnt seem to work in all cases.

Im using :

'wait until the page loads
Do While ie.Busy Or ie.readyState READYSTATE_COMPLETE
DoEvents
Loop

This doesnt seem to work well enough, since actions are occuring that should NOT occur until the browser is ready.

Any other ideas as to how to make IE wait until the page is completely ready? I wondered about possibly checking the .innerHTML to verify that a unique page element is present.


Thanks,

Scott


I am trying to filter a list of records by the value in a cell and then (for the filtered result(s)) change the value in column Y just for the filtered results. This is the code I have at present which filters the records OK but I can't fathom what I need to do to ensure I only chnage the value of the cell(s) in column Y?

[CODE]ActiveSheet.Range("$A$1:$DC$5000").AutoFilter Field:=1, Criteria1:="GI255"
'After filtering I want to change the value in column Y for only the filtered entries CODE]


Dear Sir,

If any one could give me a solution for this its will be a great help for me.

I am working in a bank and on every day we receive Month to date data of Loans and advances made by every branch with Region wise total and manager wise total. With Sum of loan and count of loan. In our MIS format the in A coulum branches are sorted and listed in a sequence with sub total Region. I have data where if if do the pivot and change the data the out put should be displayed accordingly in the MIS format. Can any one help me how can we do this or is there any other office addins/softwares are available if so the data is changed on a daily basis and if we do the pivot it will automatically copy the data to the MIS format.

Thanks.


Hey all,

Coffee hasn't hit my brain yet this morning and I cant figure this one out.

I have a list of names that are in the following format:

LastName Suffix, FirstName MI

The middle initial is not always present, neither is the suffix. For simplicity purposes, we will assume the suffix can ONLY be "Jr."

Here is some sample data:

Brown Jr., Wayne
Fowler, Michael
Parker, David A
Winstead Jr., Johnny N

I want the output to be the following:

Brown, Wayne
Fowler, Michael
Parker, David
Winstead, Johnny

Thanks in advance for the help!

PS (if i can get a formula that at least gets rid of the middle initial at the end (if it exists), then that would be a HUGE step in the right direction, as the Jr. only occurs on maybe 3% of my data. The middle initial is in about 80% of the data.


This formula is supposed to calculate difference between today and date hired to give years and months of service. Works fine for all except if the person was hired in year 2000 then this formula shows 110 y. Any one know how to fix this? or have a different formula that works.

=DATEDIF(E21,TODAY(),"y")&" y "&DATEDIF(E21,TODAY(),"ym")&" m"

Thanks
Rick


From limited experience I know that excel calculates dates via serial numbers.

I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)

I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.

What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?

Hopefully a simpler question for your experience level than mine.


I have a workbook that has compliance dates in columns "F","G" and "H" from row 7. What I need is when the date in either column comes within 30 days to auto send an email, address in column "A", recipients name in column "B". and then place todays date in column 'P". ALso need to send a follow up email when either date comes within 7days and then place todays date in column "Q". If there is a date in column "P" then don't send email. If there is a date in column "Q" then don't send follow up. Can this be done without the users intervention and each time the workbook is opened.
Thanks in advance for any assistance.

Mick


I have a sheet which has the start, stop and time taken for lunch breaks.

I want to calculate the total time worked such that i have the hours worked. So if i worked 7 hours and 30min then the total time would be 7.5hrs. But i am not sure how to take into account the lunch break time (see enclosed sheet).

anyone have any ideas?


Hello, I'm trying to count the number of times a name appears across multiple worksheets but I can't seem to figure it out.


I have 6 sheets to count and all the names are in column A (from A1:A100) on each sheet. The names are not in a particular order.

On sheet 7 I want to have a cell beside each persons name that counts the number of times their name appeared on the other 6 sheets.

For example Sheet 7 (called total) would have

Joe 5 (where 5 is the outcome of the formula I'm looking for to count all the times "Joe" appears on sheets 1-6).

I have it counting per sheet with =COUNTIF(A1:A130,"name") but this is not quite what I am looking for.

Thanks in advance for any advice.


Edit: I am using Excel 2007 w/ windows XP