Computer Help forum

General discussion

Excel Formula Problem

by learningnow / June 8, 2004 8:29 PM PDT

I am running Windows 2000 and Excel 2000. My problem is creating a formula that will calculate Days Lost from a First Date of Absence and the Return to Work Date. I need to be able to calculate the Days Lost excluding Saturday and Sunday. Here is what my spreadsheet looks like:

Date of Absence
01/05/2004

Return to Work
06/07/2004

Days Lost

Can someone please help me out with the Formula in Days Lost column?

Thank you so much!

Discussion is locked
You are posting a reply to: Excel Formula Problem
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Excel Formula Problem
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re:Excel Formula Problem
by R. Proffitt Forum moderator / June 8, 2004 10:26 PM PDT
In reply to: Excel Formula Problem

Sadly, this is no simple task. Here's why. Each business I've encountered has its own calendar and you can't use some assumption that workdays are M-F. As such, I have to create a database of the work calendar, a program for the business to create such and then more code to cound the days up.

Figure about a week to create such, 3 more weeks if documentation is required.

Bob

Collapse -
Re:Re:Excel Formula Problem
by learningnow / June 8, 2004 11:29 PM PDT

I just found out that I don't need to eliminate the Saturday and Sunday's. The request is to have a total number of days (7 day week); therefore what I need is to have the Days Lost calculated from the DATE OF ABSENCE and the RETURN TO WORK DATE.

Can this be done using a formula?

Thanks,
Kathy

Collapse -
(NT) Then why didn't you substract one from the other?
by R. Proffitt Forum moderator / June 8, 2004 11:52 PM PDT

.

Collapse -
Re:(NT) Then why didn't you substract one from the other?
by learningnow / June 9, 2004 12:16 AM PDT

I just realized this.

I guess what confused me was when I thought I needed to eliminate the Saturday and Sunday's.

Thanks everyone for all your help.
You are superb!

Collapse -
Re:Excel Formula Problem
by Kees Bakker / June 8, 2004 10:43 PM PDT
In reply to: Excel Formula Problem

I'm afraid you have to program your own function in VBA. It could be something like weekdays_in (first_date, last_date).

It is quite simple: loop from first_date to last_date. Use function weekday to see if it is weekend (saturday, sunday) or non-weekend. If the last, add 1 to function_value. I think it can be done in very few lines of code.

It takes a learning curve to program your own functions, however. Your first one might require days. Your comparable second one is done in less than an hour, and a comparable third one in minutes.

If you've never done so, see if the help or easy references from http://www.google.com/search?q=excel+write+own+functions do offer enough help. There are references to training courses and books also. Or find someone with a little bit of programming experience in Excel. It's up to you!

Hope this helps.


Kees

Collapse -
As Bob said ...
by Kees Bakker / June 8, 2004 10:55 PM PDT

it takes more code if you want to take into account things like Christmas, Bank Holiday and New Years day.

Let alone Easter and Ascension, which are on different dates each year. Let alone school holidays. No need however to have it very flexible by using a separate table, if you're willing to change the code each year (and be sure to document it, for your successor!).

Good luck,

Kees

Collapse -
Re:Re:Excel Formula Problem
by learningnow / June 8, 2004 11:24 PM PDT

I just found out that I don't need to eliminate Saturday and Sunday. I need to count the total days from the Date of Absence to the Return Date. Is this something I can do using a Formula?


Thanks for this information as I found the google link very interesting.

Collapse -
While I see your problem solved, ...
by Edward ODaniel / June 9, 2004 11:41 AM PDT
In reply to: Excel Formula Problem

Excel does have a function that assumes a 5 day work week and even allows for holidays.

Use the Excel Help to look up the NETWORKDAYS function.

Ignoring holidays and assuming your "Date of Absence" to be cell A1 and the given date of 01/05/2004 to be cell A2 with "Return to Work" in B1 and its inclusive date in B2 then putting =NETWORKDAYS(A2,B2) in cell C2 provides an answer of 111 (working days lost not counting holidays).

Popular Forums
icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

CNET FORUMS TOP DISCUSSION

Help, my PC with Windows 10 won't shut down properly

Since upgrading to Windows 10 my computer won't shut down properly. I use the menu button shutdown and the screen goes blank, but the system does not fully shut down. The only way to get it to shut down is to hold the physical power button down till it shuts down. Any suggestions?