# Subtracting 2-3 numbers depending on how many are present

#### tmo4ever

##### New Member
Hey everyone,

I'm a newbie here and have done several searches. I have a couple questions I'm hoping you can help me with.

1. I need to subtract 2 dates. However, if a third date is present, I need the formula to subtract that date rather than the initial date. EG.

A1: 1/1/05
B1: 1/6/05

D1: Should = 5 [this should be =A1-B1]

BUT if C1 contains a date I want to subtract that from B1

A1: 1/1/05
B1: 1/6/05
C1: 1/3/05

D1: Should = 2 How do I do this?

2. I need to determine a Period/Week calendar, similar to what fiscal institutions use. any ideas?

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### erik.van.geit

##### MrExcel MVP
Hi, tmo4ever
Welcome to the Board !!!!!

for the first question
Code:
``=IF(C1,C1,B1)-A1``
(to my sense you mean =B1-A1 and not =A1-B1 (6 - 1 = 5) )

for the second question
my suggestion would be not to combine two differenet questions within a single thread
anyway, can you clarify please ?

kind regards,
Erik

#### Tazguy37

##### MrExcel MVP

=IF(C1,B1-C1,B1-A1)

in D1 and copy down.

For number two, do you need to determine just the week number? ISO week number?

Chip Pearson has some VBA functions you can use for that on his page here. Hope that helps! Post back if you have any problems using the posted code.

#### tmo4ever

##### New Member
Thanks Erik! I have the First problem all set. Good advice on the second issue too

A fiscal calendar measures the year in periods and weeks, rather than months and weeks.

For example: Today is November 7th in the fiscal calendar it is Period 11 week 2.

I have a series of dates that are populating an I want to assign a period and week to automatically.

#### tmo4ever

##### New Member
Thanks for both formulas! One more add on question.... How could I work the "Network Days" aspect into that. Essentially I want to know the number of Business days between the two dates.

Thanks!

Replies
18
Views
345
Replies
3
Views
410
Replies
4
Views
312
Replies
3
Views
149
Replies
0
Views
68

1,195,598
Messages
6,010,646
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back