I have a holidays and absence spread sheet at work, The holidays count down from 25 to 1 every time a days holiday is entered into a cell.

What i want help with is i need to create a formula to count down in hours.

Can anyone help.

Thanks

Dave

Go to the field with the number of day formula and modify the formula just add to it this *8. The result will be in hrs.

Assuming a holiday uses 8 hrs of pay. It sounds like everyone starts with 25 holiday X 8 hrs or 200 total hours,

Posted on Jun 27, 2008

Hi,

a 6ya expert can help you resolve that issue over the phone in a minute or two.

best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.

the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).

click here to download the app (for users in the US for now) and get all the help you need.

goodluck!

Posted on Jan 02, 2017

The WorkDay Function returns a number that's the serial date that is the indicated number of working days from a given date (the starting date). Working days EXCLUDE weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.

The Syntax is:

=WORKDAY(start_date,days,holidays)

where:

- start_date is in Date format (and can be a calculated value);

- days is a number of elapsed days after start_date (can be calculated, can be negative to indicate a date BEFORE start_date);

- holidays is an array of holidays you can specify if desired.

The Syntax is:

=WORKDAY(start_date,days,holidays)

where:

- start_date is in Date format (and can be a calculated value);

- days is a number of elapsed days after start_date (can be calculated, can be negative to indicate a date BEFORE start_date);

- holidays is an array of holidays you can specify if desired.

May 01, 2014 | Microsoft Excel for PC

This is too complicated for a help site like this, you are going to want to consult an excel forum, or some excel professional in your area. This is far beyond the scope of what you can expect for $15, which I'm sure you now realize after 10 days of trying.

Thanks

Tim

Thanks

Tim

Sep 26, 2010 | Microsoft Excel for PC

Hello this is Baris,

Can you tell me more about this employee. Are you creating an excel spreadsheet to keep record of his days. Are you just getting the numbers from an outside source like a schedule.

To be able to give you an answer I will assume that you have the info already in the same spreadsheet.

Lets say Column A is the days of the month.

Column B is the information column like the hours that the employee worked. If the month has 31 days this is what you need to do.

Click on the cell B32 and type the formula

=count(B1:B31) and press enter. This will count the number off cells which have a value in it in that month.

If you provide me more info we may come up with a better solution. Hope this helps :)

Can you tell me more about this employee. Are you creating an excel spreadsheet to keep record of his days. Are you just getting the numbers from an outside source like a schedule.

To be able to give you an answer I will assume that you have the info already in the same spreadsheet.

Lets say Column A is the days of the month.

Column B is the information column like the hours that the employee worked. If the month has 31 days this is what you need to do.

Click on the cell B32 and type the formula

=count(B1:B31) and press enter. This will count the number off cells which have a value in it in that month.

If you provide me more info we may come up with a better solution. Hope this helps :)

Sep 24, 2009 | Microsoft Excel for PC

This is a long shot ... I dont know what spread sheet you are having trouble with. It might be that your sheet thinks you are entering text when you put in the reference "J~" Be dure you are entering a formuila and not test that looks like a formula (to you)

Sep 14, 2009 | Computers & Internet

Your 28 days holiday is free holidays (assuming you have not including predetermined holidays - i.e public holidays)

28 days divide by 12 months = 2.333 days

Your holiday year cannot be April to April as this is 13 months - so I have assumed it is April to March, or it could be May to April - This will affect how many holidays you have earnt upto August.

Multliply the 2.33 days per month by the number of months until your holiday in August. The calculation your employer is using may take into account you have to earn full days by the end of the month before you take a holiday.

2.33 days X 4 months to end of July = 9 days

2.33 days X 5 months to end of August - 11 days.

You may have to exclude the public holidays if this is included in your annual allowance

28 days divide by 12 months = 2.333 days

Your holiday year cannot be April to April as this is 13 months - so I have assumed it is April to March, or it could be May to April - This will affect how many holidays you have earnt upto August.

Multliply the 2.33 days per month by the number of months until your holiday in August. The calculation your employer is using may take into account you have to earn full days by the end of the month before you take a holiday.

2.33 days X 4 months to end of July = 9 days

2.33 days X 5 months to end of August - 11 days.

You may have to exclude the public holidays if this is included in your annual allowance

Apr 15, 2009 | Microsoft Office Standard for PC

Here is one way to do it:

1. In column A enter the name

2. In column B enter their start date in the format day-month-2008 (31-12-2008 for Dec. 31, 2008); if a day is less than 10, put zero in front (03, 04, etc.)

3. In column C put 31-12-2008 (December 31, 2008, the last day of the year)

4. In column D, put C1-B1 and format as a number with 2 places after the period (10.25 for example) if you want to track partial days, other format for no zeroes after the period (13)

5. In column E, put this formula:

(D2/30)*1.25 (assuming this is line 2)

You can then copy these lines as many times as you need to, total them, etc.

1. In column A enter the name

2. In column B enter their start date in the format day-month-2008 (31-12-2008 for Dec. 31, 2008); if a day is less than 10, put zero in front (03, 04, etc.)

3. In column C put 31-12-2008 (December 31, 2008, the last day of the year)

4. In column D, put C1-B1 and format as a number with 2 places after the period (10.25 for example) if you want to track partial days, other format for no zeroes after the period (13)

5. In column E, put this formula:

(D2/30)*1.25 (assuming this is line 2)

You can then copy these lines as many times as you need to, total them, etc.

Dec 27, 2008 | Microsoft Office Standard for PC

Hi Chinnu,

For this you will have to install an Add-in called Analysis Tool Pack.

Kindly follow the below instructions:

1. Open Excel Options (Menu-Excel Options)

2. Click on Add-Ins on the side pane of the Excel Options Window

3. Check to see if Analysis ToolPak is in Inactive application add-in list.

4. If is not, proceed to Step 7

5. If it is in the inactive application add in list, Select Excel Add-ins against Manage and click on "Go"

6. In the add-ins menu check Analysis ToolPak and Analysis ToolPak VBA and click OK

7. Use this formula to find the number of work days excluding Saturday & Sunday in a month

=NETWORKDAYS(start_date,end_date, [Holidays])

In your case give the start date as 1st July and end date as 10th July and Holidays as 0 or 1 (doesn't matter). The answer it will return will be 8 days.

Trust the above helped.

For this you will have to install an Add-in called Analysis Tool Pack.

Kindly follow the below instructions:

1. Open Excel Options (Menu-Excel Options)

2. Click on Add-Ins on the side pane of the Excel Options Window

3. Check to see if Analysis ToolPak is in Inactive application add-in list.

4. If is not, proceed to Step 7

5. If it is in the inactive application add in list, Select Excel Add-ins against Manage and click on "Go"

6. In the add-ins menu check Analysis ToolPak and Analysis ToolPak VBA and click OK

7. Use this formula to find the number of work days excluding Saturday & Sunday in a month

=NETWORKDAYS(start_date,end_date, [Holidays])

In your case give the start date as 1st July and end date as 10th July and Holidays as 0 or 1 (doesn't matter). The answer it will return will be 8 days.

Trust the above helped.

Jul 23, 2008 | Microsoft Office Professional 2007 Full...

Nope, sorry, although I am truly an expert at Excel formulas, I do not understand what you are trying to end up with in the final cell. We can compare a specified field with two spreadsheets - use named ranges and index/match lookup formulas. But then where you really lose me is in reading "a generic field" to find a match, and then placing what "data from another field" into what "other sheet" - ? See the confusion?

Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:

Sample Data (columnar arangement):

A1: Part B1: Code C1: Price D1: Find Part E1: Find Code

A2: x B2: 11 C2: 5.00 D2: y E2: 12

A3: x B3: 12 C3: 6.00 D3: y E3: 11

A4: y B4: 11 C4: 7.00 D4: x E4: 12

A5: y B5: 12 C5: 8.00 D5: x E5: 11

To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.

To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:

=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))

Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.

Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!

Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:

Sample Data (columnar arangement):

A1: Part B1: Code C1: Price D1: Find Part E1: Find Code

A2: x B2: 11 C2: 5.00 D2: y E2: 12

A3: x B3: 12 C3: 6.00 D3: y E3: 11

A4: y B4: 11 C4: 7.00 D4: x E4: 12

A5: y B5: 12 C5: 8.00 D5: x E5: 11

To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.

To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:

=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))

Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.

Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!

Jul 08, 2008 | Microsoft Computers & Internet

at first select the 1st page data and select data-subtotal from the upper menu list.

then enter the formula as =sum(1stpageSubTotal,2ndpageSubTotal,3rdpageSubTotal) then Press Enter.

If not solved pls get me that data as to reference.

thanks

then enter the formula as =sum(1stpageSubTotal,2ndpageSubTotal,3rdpageSubTotal) then Press Enter.

If not solved pls get me that data as to reference.

thanks

Dec 04, 2007 | Microsoft Excel for PC

Hi,
I guess you mean vacation accrual. It's hard to help you here, without knowing the exact accrual formula.
However here are some tips:
Set up a column with the start date for each employee and use the today() function to get today's date.
You can use the days360() funtion to calculate the number of days between two dates, assuming regular 30 days/month.
For more date related functions, click on the Fx (letter f with an subscript x) to the left of the value/formula entry field at the top of the spreadsheet. Thsi gives you a wizard with a list of functions. Select the Data/Time group and you see all available functions and can interactively pull them together.
Good luck
K

Sep 05, 2007 | Microsoft Office Standard for PC

143 people viewed this question

Usually answered in minutes!

I have a holidays and absence spread sheet at work, The holidays count

down from 25 to 1 every time a days holiday is entered into a cell.

What i want help with is i need to create a formula to count down in hours.

Can anyone help.

Thanks

Dave

×