(Msg. 1) Posted: Sun Mar 02, 2008 2:37 pm
Post subject: Formulas Archived from groups: microsoft>public>excel>newusers (more info?)
I'm trying to create a formula to minus one date from the next. So i've done
the standard equation in column d (=c1-b1) and i get my answer. but in some
cases some cells in column b are not filled in. so in column d i get these
really big numbers. i dont want those numbers there so i was wondering how i
get rid of them. also these two things im minusing are dates. i was wondering
if there was any ways to create the formula to only include work week days so
if cell b1 is friday january 4 2008 and c1 is monday january 7 2008. i want
cell d1 to say 1 and not 3. is this possible.
I hope someone can understand this. And any help would be much appreciated.
(Msg. 2) Posted: Sun Mar 02, 2008 3:00 pm
Post subject: RE: Formulas [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
if the analysis toolpak is installed, try:
=IF(B1,NETWORKDAYS(B1,C1)-1,"")
"Stephanie" wrote:
> I'm trying to create a formula to minus one date from the next. So i've done
> the standard equation in column d (=c1-b1) and i get my answer. but in some
> cases some cells in column b are not filled in. so in column d i get these
> really big numbers. i dont want those numbers there so i was wondering how i
> get rid of them. also these two things im minusing are dates. i was wondering
> if there was any ways to create the formula to only include work week days so
> if cell b1 is friday january 4 2008 and c1 is monday january 7 2008. i want
> cell d1 to say 1 and not 3. is this possible.
>
> I hope someone can understand this. And any help would be much appreciated.
(Msg. 3) Posted: Sun Mar 02, 2008 3:28 pm
Post subject: Re: Formulas [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
C1 is January 7, 2008
B1 is January 4, 2008
=IF(B1="","",C1-B1) for the first part returns 3
For the second part you could use the NETWORKDAYS function from the Analysis
Toolpak Add-in.
=IF(B1="","",NETWORKDAYS(B1,C1))
returns 2 because Friday is a workday and Monday is a workday and NWD function
counts both.
NOTE: you must load the ATP through Tools>Add-ins.
BTW.........the big numbers are just the serial numbers that Excel uses to keep
track of dates/times.
Gord Dibben MS Excel MVP
On Sun, 2 Mar 2008 14:37:01 -0800, Stephanie
<Stephanie RemoveThis @discussions.microsoft.com> wrote:
>I'm trying to create a formula to minus one date from the next. So i've done
>the standard equation in column d (=c1-b1) and i get my answer. but in some
>cases some cells in column b are not filled in. so in column d i get these
>really big numbers. i dont want those numbers there so i was wondering how i
>get rid of them. also these two things im minusing are dates. i was wondering
>if there was any ways to create the formula to only include work week days so
>if cell b1 is friday january 4 2008 and c1 is monday january 7 2008. i want
>cell d1 to say 1 and not 3. is this possible.
>
>I hope someone can understand this. And any help would be much appreciated.
(Msg. 4) Posted: Fri Sep 11, 2009 1:33 pm
Post subject: RE: Formulas [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I need help with something similar. I need to calculate turn around time by
number of days D2 being the start and W2 being the end date, with out the
lasrge numbers when there is no date. I have to gather these totals days from
several worksheets and calcualte their total on another spreadsheet Sheet!5.
Any help would be appreciated.
"JMB" wrote:
> if the analysis toolpak is installed, try:
> =IF(B1,NETWORKDAYS(B1,C1)-1,"")
>
>
> "Stephanie" wrote:
>
> > I'm trying to create a formula to minus one date from the next. So i've done
> > the standard equation in column d (=c1-b1) and i get my answer. but in some
> > cases some cells in column b are not filled in. so in column d i get these
> > really big numbers. i dont want those numbers there so i was wondering how i
> > get rid of them. also these two things im minusing are dates. i was wondering
> > if there was any ways to create the formula to only include work week days so
> > if cell b1 is friday january 4 2008 and c1 is monday january 7 2008. i want
> > cell d1 to say 1 and not 3. is this possible.
> >
> > I hope someone can understand this. And any help would be much appreciated.
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office