Tuesday, June 25, 2013

Google Spreadsheet how to calculate remaining working days in a month

Number of days left in a month, excluding weekend (Saturdays and Sundays)

Question:

Based on today's date, I'm trying to determine what formula to use to tell me how many days are left in a month, excluding weekends. 

I basically want the spreadsheet to take today's date and give me the number of days excluding weekends. 

Thanks for any help. I've been looking around for a little while today and can't come up with anything. 

Solution:

When you are trying to calculate how many days you have left before reaching a project deadline, try the following formula of Spreadsheet.
You don’t need to count out days on a calendar to find out how many days you have to complete a project. Google Spreadsheet’s NETWORKDAYS function can calculate it for you — without requiring you to work weekends or holidays.

Try the following formula:

=NETWORKDAYS(TODAY();EOMONTH(TODAY();0))

Explaining NETWORKDAYS:
NETWORKDAYS(start_date, end_date, holidays)
Returns the number of workdays between start_date and end_date. Holidays can be deducted. Start_date is the date from which the calculation is carried out. End_date is the date up to which the calculation is carried out. If the start or end date is a workday, the day is included in the calculation. Holidays (optional) is a list of holidays. Enter a cell range in which the holidays are listed individually.

Explaining EOMONTH:
EOMONTH(start_date, months)
Returns the date of the last day of a month which falls Months away from the given Start_date. Months is the number of months before (negative) or after (positive) the start date.

No comments:

Post a Comment