LinkBack URL
About LinkBacks
Hi guys,
Is there anyway to convert this so it’s compatable in 2003 excel?
=IF(WEEKNUM(AK7)=WEEKNUM(AK8),AM7,AM7 +1)
Thanks
Hi guys,
Is there anyway to convert this so it’s compatable in 2003 excel?
=IF(WEEKNUM(AK7)=WEEKNUM(AK8),AM7,AM7 +1)
Thanks
Last edited by AliGW; 10-19-2017 at 05:53 AM.
BBUMP, Anyone?
Lee - please do not bump threads until a day has passed.
Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. If you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.
From the forum rules (link in the main menu above):
- If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.
Last edited by AliGW; 10-19-2017 at 08:01 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Oh right sorry, my bad!
Last edited by AliGW; 10-19-2017 at 08:01 AM. Reason: Unnecessary quotation removed.
Try
To use WEEKNUM you need to install the Analysis ToolPak from Tools > Add-Ins.
I never had it before on other workbook, and it works it’s just need it on 2003 excel, can the formula not just be changed? Or..Originally Posted by JohnTopley
Can’t seem to install it on work computer because of the security, is there anyway round using them WEEKNUM?
Assuming 1st Jan is (first day of) week1 then
=INT((A1-EOMONTH($A$1,-1)-1)/7)+1
A1=01/01/year
will calculate a week number
It will need to be "re-set" for 1st Jan following year
Does this help?
1st day of the week is Sunday, 2nd is Monday.. 1-7 Sunday to Saturday
Last edited by AliGW; 10-19-2017 at 01:11 PM. Reason: Quotation removed - not needed.
..so first Sunday in January is start of week 1 ?
1st Jan 2017 is a Sunday! (by luck!).
Do you actually want to replicate WEEKNUM function exactly or have every week starting on a Sunday? Those two are not the same because WEEKNUM defines week 1 as starting on 1st Jan, whatever the day of week, with week 2 starting on the next Sunday ( so week 1 could be as short as 1 day)
if it's the latter then you don't need WEEKNUM function at all - just use this formula:
=AM7+(AK7-WEEKDAY(AK7-1)<>AK8-WEEKDAY(AK8-1))
Audere est facere
Here, I googled it for you:
Formula:
Please Login or Register to view this content.
https://msdn.microsoft.com/en-us/lib...ffice.12).aspx
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
What is A1 though? 🤔 It’s hard to explain what I need
I need every week starting on a Sunday, basically need to replicate WEEKNUM.. as it works perfectly on excel 2007 my comp but at work it doesn’t as it’s 2003
Like I said, though, those are not the same - using WEEKNUM function means that every year 1st Jan and the day before (31st Dec) will be deemed to be in different weeks, even if 31 Dec is a Wednesday, for example, and 1st Jan a Thursday. If you want those two days to be counted as the same week in your formula then you can use the formula I suggested
Last edited by AliGW; 10-19-2017 at 01:10 PM. Reason: Quotation removed - not needed.
=AM7+(AK7-WEEKDAY(AK7-1)<>AK8-WEEKDAY(AK8-1))
Is just returning #NUM! Though?
Last edited by AliGW; 10-19-2017 at 01:09 PM. Reason: Quotation removed - not needed.
Right when I get home, I’ll send you the workbook to have a look
Lee - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
Oh right, sorry ali.. breaking so many rules today haha!
File seems to be a little big so will link through Dropbox
https://www.dropbox.com/s/l1y94vh2h4...LIDAY.xls?dl=0
The formula needed is on overview in column AM8:AM150 , The previous formula is currently there showing how i need it to work, thanks
Tried it, not showing the first week on the left side sadly, where it shows all the holidays as you could see
Last edited by LeeBillington; 10-19-2017 at 01:53 PM.
OK, try this formula in AM8 copied down
=AM7+IF(COUNT(AK7,AK8)=1,1,(AK7-WEEKDAY(AK7-1)<>AK8-WEEKDAY(AK8-1)))
It worked!!will that work on 2003 for sure though? as I am now trying it on 2007 and its working but you experts know whether it will work or not.
If so! you my friend are a legend!
Yep, WEEKDAY and COUNT will work fine in 2003It worked!!
Hey Jacc, when I googled it I got a shorter version!
Well I'll be darned!
So I’m kinda having a problem with the formula, as it’s putting 01/04/17 a Saturday and 02/04/17 a Sunday on the same row when they should be separate as it’s two different weeks, Sunday should be on one week as it’s the new week and Saturday should be on the previous row as it’s a Saturday before that week, it’s giving the Saturday nd Sunday both week 1, also when I put 01/04/17 , 02/04/17 and 03/04/17 it’s Sunday Monday Tuesday but Monday and Tuesday go onto a different row
Last edited by LeeBillington; 10-20-2017 at 05:45 AM.
I suggest you post a sample file.
Attach a sample workbook (not image).
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
content deleted by poster.
Last edited by LeeBillington; 10-21-2017 at 05:59 AM.
Lee - attach the files here, please.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Both failing , the workbooks are too big i believe.
Then make them smaller! You only need samples that show the issues. Nobody should have to download massive workbooks in order to help you!
And how do i do that? there is no unneeded stuff within the workbook..
Lee - make a smaller version of the real thing. A SAMPLE workbook. A few lines of relevant data will be enough.
That wont show what i need tho.. no one seemed to have a problem with downloading the last one anyway?
As you wish. Some people are unable to access file-sharing sites, and some will not download massive files. Your choice. You can easily show what you need to show without sending the entire dataset.
Try "ZIPPING" the file which allows much larger files to be posted.
However, you should, as already pointed out by Ali, be able to send a sample file as we are apparently only interested in relatively "simple" date-related formula.
So I've attached the way its coming up, and the way i kinda need it
the formula is still in AM8 downwards on overview sheet
but the place its coming out wrong is I9:V12
The correct way. displaying how i need it with the old formula
The Incorrect way. displaying how its coming up with the new one.
You'll see the difference in results on both pages
thanks
Is your logic for "Right" correct: add 2 May as a holiday and it says this is week 3? Is the logic only showing dates in a "different" week as opposed the actual "weeknum" week?
The logic only works if the days are sequential with no gaps ?
Put =WEEKNUM(AK8) in AN8 and copy down , adding holiday date of 2 May.
Last edited by JohnTopley; 10-22-2017 at 04:24 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks