Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 2nd 06, 06:15 PM posted to microsoft.public.excel.misc
Heyna
 
Posts: n/a
Default Removing Weekend dates from Day Calculations


I'm trying to use date formulas to show the amount of days between an
invoice date and a Due Date. I'm using DAYS360 to figure out the number
of days between the two dates, and I'm trying to figure in a 5 day
"Grace period" before the time shows late and the customer gets a late
fee. I was using an "IF" formula for the grace period (ex: =IF(A15,
true,false)) My problem is, of course, Weekends. Since this formula to
figure out days late will continue to tally until payment is made, we
can be off 2 days every 5 after being late, or it will show late early
if the invoice goes out on Wednesday.
Anyone know of a formula or a way to execute what I'm trying to do?
Much thanks
Scott


--
Heyna
------------------------------------------------------------------------
Heyna's Profile: http://www.excelforum.com/member.php...fo&userid=8148
View this thread: http://www.excelforum.com/showthread...hreadid=518335


  #2   Report Post  
Old March 2nd 06, 06:28 PM posted to microsoft.public.excel.misc
JE McGimpsey
 
Posts: n/a
Default Removing Weekend dates from Day Calculations

Do you really need to use DAYS360? That can cause you to be off since it
assumes 12 months of exactly 30 days.

You can figure out the total number of days just by subtracting the
invoice date from the due date (and formatting as Number or General).

To exclude weekends, take a look at NETWORKDAYS() in XL Help. It's an
Analysis Toolpak Add-in function so you'll need to load the ATP
(Tools/Add-ins...).

In article ,
Heyna wrote:

I'm trying to use date formulas to show the amount of days between an
invoice date and a Due Date. I'm using DAYS360 to figure out the number
of days between the two dates, and I'm trying to figure in a 5 day
"Grace period" before the time shows late and the customer gets a late
fee. I was using an "IF" formula for the grace period (ex: =IF(A15,
true,false)) My problem is, of course, Weekends. Since this formula to
figure out days late will continue to tally until payment is made, we
can be off 2 days every 5 after being late, or it will show late early
if the invoice goes out on Wednesday.
Anyone know of a formula or a way to execute what I'm trying to do?
Much thanks
Scott

  #3   Report Post  
Old March 3rd 06, 01:41 AM posted to microsoft.public.excel.misc
daddylonglegs
 
Posts: n/a
Default Removing Weekend dates from Day Calculations


If your invoices always go out on weekdays your 5 days grace will be the
equivalent of 7 actual days, won't it?

...unless you want to factor in holidays, in which case I believe you'd
want NETWORKDAYs as suggested by JE McGimpsey


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=518335



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to pull current dates from list w/many dates mcilpuf Excel Discussion (Misc queries) 4 February 20th 06 10:05 AM
need to convert list of dates to count no. of dates by week neowok Excel Worksheet Functions 13 January 30th 06 04:54 PM
Difference in dates calculations except between certain times. Steve Hud Excel Discussion (Misc queries) 1 January 13th 06 02:10 PM
Calculating number of days between two dates that fall between two other dates [email protected] Excel Discussion (Misc queries) 5 October 26th 05 06:18 PM
Shading row with weekend dates DKerr Excel Discussion (Misc queries) 7 September 29th 05 07:50 PM


All times are GMT +1. The time now is 10:20 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017