Formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stephanie - 02 Mar 2008 22:37 GMT

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.

Reply to this Message

JMB - 02 Mar 2008 23:00 GMT

if the analysis toolpak is installed, try:
=IF(B1,NETWORKDAYS(B1,C1)-1,"")

> 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
[quoted text clipped - 6 lines]
>
> I hope someone can understand this. And any help would be much appreciated.

Reply to this Message

Gord Dibben - 02 Mar 2008 23:28 GMT

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

>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
[quoted text clipped - 6 lines]
>
>I hope someone can understand this. And any help would be much appreciated.

Reply to this Message

Rate this thread: