Subtracting 2-3 numbers depending on how many are present

[tmo4ever]

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?

Thanks for your help!

 

[erik.van.geit]

Hi, tmo4ever
Welcome to the Board !!!!!

for the first question

=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]

How about:

=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]

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]

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!

 

[erik.van.geit]

on the page Tazguy gave you there is a link
http://www.cpearson.com/excel/DateTimeWS.htm
which will lead you to networkdays ...