Question about Microsoft Excel for PC

1 Answer

Converting date of a month to last working day of the month

I want to calculate date of retirement of some staff members. But the problem is that it is calculating date of retirement from date of birth by adding either 60 years or 58 years as the case may be. But the date remains the same as given in the date of birth of the person. I want to make the date to be the last working day of the month. Please help.

Posted by on

1 Answer

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    MVP:

    An expert that gotĀ 5 achievements.

    Governor:

    An expert whose answer gotĀ voted for 20 times.

    Hot-Shot:

    An expert who has answered 20 questions.

  • Expert
  • 58 Answers

Use the below formula to get the date of the LWD (Monday thru Friday) of the month.
Assumed that if the date in cell A1 is 6-May-2005. then formula will be
=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))

Posted on Jan 31, 2009

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Workday function in excel formulas


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.

May 01, 2014 | Microsoft Excel for PC

1 Answer

Need a formula to calculate length of service in excel


You could do a simple +NOW-START DATE

It would depend on the format of your start dates - i.e YY, DD/MM/YY as to the results you get.

Do want the results in years, months, days?




Jun 05, 2009 | Microsoft Excel for PC

1 Answer

Calculate Quarters between any two dates


not sure if this is what you are after entirely but should at least help you out some what

=DATEDIF(A3,C3,"m")/3

As you said you cant just divide by 3 but I have divided the formula by 3 to see how many 3 months will go into the output you could maybe have another formula to divide it by 2 to see if it will divide by 2 to see if you get a whole number or not, if you do then you can just return the first part before the dot ( using split function ) and if the value is less then 1 then obviously the 3 months has not passed between the 2 dates

May 09, 2009 | Microsoft Office Excel 2003 for PC

1 Answer

How to calculate the difference of two dates by year, month and day using the formula in excel?


To calculate the difference in Years use =Year(c,r)-Year(c,r)
To calculate the difference in Months use =Month(c,r)-Month(c,r)
To calculate the difference in Months use =(c,r)-(c,r)

c= column, r= row

You will need to format the source cells as dates

Mar 15, 2009 | Microsoft Office Excel 2003 for PC

1 Answer

Calculation of end date in format dd/mm/yy


Assuming you put the start date in cell A1, put this formula:

=DATE(YEAR(A1),MONTH(A1)+15,DAY(A1))

in the cell where the result wanted to be appeared.

P.S.: If this information was helpful, please rate this solution.

Mar 12, 2009 | Microsoft Office Excel 2003 for PC

1 Answer

QuickBooks 2003 Payroll Question


Hi,
Without looking at your books, I can only hazard a few guesses. One is that the person is salaried and by pay date the calculation is figuring on an extra payweek. There are 53 Sun, Mon, Tue and Wednesdays in this year. Check the math and see if that is the problem (to correct add an extra pay week into the salaried amount in the employee profile)

Jan 07, 2009 | Intuit QuickBooks Pro 2003 Single User...

2 Answers

Formula for calculating the days beween months


for example, cell A1 has date (01-01-2008) and cell A2 has current date (08-24-2008) and cell A3 shows total days, is that you want to know? if yes, apply formula as under...

cell A3......(properties set as General to show digits)................ =SUM(A2-A1)

Aug 25, 2008 | Microsoft Office Professional 2007:...

1 Answer

Access


There is no function to do this as part of a whole suite of date manipulation functions, but I seem to have imagined that, as I couldn’t find. I was dreading having to do lots of nasty date arithmetic, but then I discovered that the zeroth day of a month is treated as the last day of the previous month! So the last day of the month for the date that’s in cell B3 is simply:
=DATE(YEAR(B3), MONTH(B3)+1, 0)
It even works across year boundaries, so DATE(2004, 13, 0) really does give

Mar 11, 2008 | Business & Productivity Software

1 Answer

Functions


The eomoth function returns the last day of the month.
Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

eomonth (1/2/08, 1) will return 31. The number of days in the month.

eomonth (1/2/08, 2) will return 59, The number of days in the month plus the next month (February)

Mar 06, 2008 | Microsoft Excel for PC

1 Answer

Creating a holiday accrual spreadsheet.


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

Not finding what you are looking for?
Microsoft Excel for PC Logo

236 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18298 Answers

Tony

Level 3 Expert

2598 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...