Formula for counting weeks /year

[dagge]

Hello all!

Need some help with the following prob I've got.

I have an education that costs say $100/week for the students.
The education is 4-8 months, and starts when I have a full class.
So to calculate my budget I need to now how many weeks the education has been per year.
I.e. If one class starts on dec.1 year 2004, and ends jan.14 year 2005, then there has been 4 weeks for year 2004 (i.e. $400) and 2 weeks for year 2005 ($200).

Which formula do I use?
I know which formula I need to use just to count weeks, but how do I separate them every year (so to speak).


Hope that the above is clear enough...


//D

 

[dagge]

Thanks for the help Erik, again

But....

I changed the & YEAR, but the formula only works when it doesn't span over a year. As soon as it spans over two years (i.e. 2003-11-01 to 2004-02-14), then the formula cracks.... Still #WRONG VALUE or similar (in swedish #VÄRDEFEL).

So I'm guessing it has to do with some setting that has to be done in Excel'97 for me. In that case: what & where? I have tried a lot of different settings, with the same result.

I can see in the file you sent me that it works just fine, but as soon as I write down a different datevalue in the cells then the formula cracks

Suggestions?

//Dagge

 

[erik.van.geit]

Dagge,

You have a dateformat in those cells which isn't logic, since the output will be weeks, expressed in decimals. That's Paddys formula which I didn't change. But this won't change your VALUE-problem.

it's extremely bizar
whatever I'm doing within a row, after that the valueproblem gets solved even if I undo the last operation !

two examples
FOR THE EXPERTS: we are talking about Paddys formula
=IF(D2,((DATEVALUE("31/12/"&YEAR(A2))-A2)/7),(B2-A2)/7)
example 1
I delete a ")" and put it in again (so in fact I don't make any changes)
example 2
I changed the date in B2 and then "undo"
in both case the problem disappears

Dagge, your problem is the opposit, as soon as you change something (even when you undo) the problem occurs, isn't it ?
I've never seen Excel 97, but can imagine it must be DATEVALUE("31/12/" which generates the error
try substituting with this : DATEVALUE("31-12-"

regards,
Erik

 

[dagge]

Yupp!

That's the problem! As soon as I change something (and even restore it back/undo) the formula cracks

Nope, didn't work with DATEVALUE("31-12-"-change either.

Now I'm even trying on a friends computer with Excel XP, but we get the same result.

Is there another way around this? i.e. another (maybe more complex) formula that does the same ?

I really appreciate your help Erik,

//D

 

[erik.van.geit]

Dagge,
you're welcome

So it could be a swedish problem...
Did you look to your helpfiles for dateformatting ?

try first to find out what's working for you
=DATEVALUE("31/12/04") or =DATEVALUE("31dec2004") or ... "31-dec-2004" or "2004/12/31" or ...

when AddIn Analysis ToolPak is not installed dateproblems can occur but normaly not with the function datevalue ==> so you never know: if Analysis Toolpak is not installed, see what you get after installation !?
menu Tools / AddIns

to my opinion you can't turn around the problem, because you need the datevalue of the end of the current year
you could as long as your problem isn't solved change "DATEVALUE("31/12/"&YEAR(A2))" to "38352": this is the value for 31-12-2004 (next two years you can add 365 and then 366) in the same way change the value in the next column to "38353"

Aladin Akyurek,
you're probably the one who knows enough swedish to solve this one !?
dagge, you could send Aladin a PM to ask him to watch your problem too.

regards,
Erik
(will probably not be online for 24 hours)

 

[dagge]

YES!!

It worked with this solution: "2004/12/31"

Many thanks Erik!! ray:


//Dagge

 

[erik.van.geit]

you're welcome,
I'm so glad it helped !

regards,
Erik

 

[dagge]

Here we go again! :wink:

After the above was cleared I started to fill in all the info that is needed.
Then I realised that every week only has 5 days, i.e. 5 days of education. But the formula counts on a week based on 7 days.
I have tried to change the formula a bit, but no success

Here's the info:
Cell D4 (which course): AUB
Cell E4 (which course): FUB
Cell G4 (start): 2004-04-26
Cell H4 (ends): 2004-05-07
Cell I4 (courselength): =AVKORTA((H4-G4)/7)&" v. "&REST(H4-G4;7)&" d."
RESULT= 1v 4d
Cell J4 (over 1 year?): =ÅR(G4)<>ÅR(H4)
RESULT= FALSE
Cell K4 (sum of weeks yr 1 on AUB): =OM(D4>1;OM(J4;((DATUMVÄRDE("2004-12-31")-G4)/7);(H4-G4)/7);"")
RESULT= 2
Cell L4 (sum of weeks yr 2 on AUB): =OM(D4>1;OM(J4;(H4-DATUMVÄRDE("2005-01-01"))/7;"");"")
RESULT= 0
Cell M4 (sum of weeks yr1 on FUB): =OM(E4>1;OM(J4;((DATUMVÄRDE("2004-12-31")-G4)/7);(H4-G4)/7);"")
RESULT= 0
Cell N4 (sum of weeks yr 2 on FUB): =OM(E4>1;OM(J4;(H4-DATUMVÄRDE("2005-01-01"))/7;"");"")
RESULT= 0
Cell O4 (price/week): 1000
Cell P4 (total price): =OM(D4>1;SUMMA(K4;L4)*O4;SUMMA(M4;N4)*O4)
RESULT= 1571

Since the week is based on 7 days, then the total price shows wrong.
In the above formula the total price should be 2000, but it becomes 1571


Erik! Do you have a golden solution?
Or maybe someone else?

//Dagge

 

[fairwinds]

Hej Dagge!

I think you could solve it with NETWORKDAYS, Check it out in help file.

It belongs to analysis toolpack, wich must be enabled, so you should not translate that function to Swedish.

=IF(D4>1,IF(J4,(NETWORKDAYS(G4,--"2004-12-31")/5),NETWORKDAYS(G4,H4)/5),"")

=OM(D4>1;OM(J4;(NETWORKDAYS(G4;--"2004-12-31")/5);NETWORKDAYS(G4;H4)/5);"")

Further you can omit the DATEVALUE/DATUMVÄRDE function

 

[fairwinds]

... and check out the DATEDIF function in help file, might be what you need.
(It's called DATEDIF in swedish too, don't know why)

 

[Aladin Akyurek]

...

=IF(D4>1,IF(J4,(NETWORKDAYS(G4,--"2004-12-31")/5),NETWORKDAYS(G4,H4)/5),"")

=OM(D4>1;OM(J4;(NETWORKDAYS(G4;--"2004-12-31")/5);NETWORKDAYS(G4;H4)/5);"")

Further you can omit the DATEVALUE/DATUMVÄRDE function

Agree with omitting DATEVALUE/DATUMVÄRDE. We can also dispense with the -- coercer though. It seems to me that the OP can avoid lots of trouble by putting the constant dates in cells of their own and refer to those cells in the formula instead of plugging them in.