Weeknum help
I am having trouble calculating the fiscal weeks of the current year. In my
company's calendar, FW1 starts on Monday, January 2, 2006. I found the weeknum function and I keep having it tell me that FW 1 is on 1/1/06. Does anyone else know of other functions I can use or do I have to do some programming to get this straightened out? Added bonus question: If I dynamically link a cell that contains a date from MS Project to Excel, how can I format the cell in Excel to display the FW not the date. Any help with either of the questions would be greatly appreciated. |
Weeknum help
If FW1 begins on 1/2/06, then you could calculate a given date's FW as
=INT((A1+7-DATE(2006,1,2))/7) (with the chosen date in A1) Alternately, with a weeknum in A1, that week begins on =date(2006,1,2)+7*(a1-1) (be sure to format the result as a date). "SoSoExcelGuy" wrote: I am having trouble calculating the fiscal weeks of the current year. In my company's calendar, FW1 starts on Monday, January 2, 2006. I found the weeknum function and I keep having it tell me that FW 1 is on 1/1/06. Does anyone else know of other functions I can use or do I have to do some programming to get this straightened out? Added bonus question: If I dynamically link a cell that contains a date from MS Project to Excel, how can I format the cell in Excel to display the FW not the date. Any help with either of the questions would be greatly appreciated. |
Weeknum help
Thanks a million for the help
"bpeltzer" wrote: If FW1 begins on 1/2/06, then you could calculate a given date's FW as =INT((A1+7-DATE(2006,1,2))/7) (with the chosen date in A1) Alternately, with a weeknum in A1, that week begins on =date(2006,1,2)+7*(a1-1) (be sure to format the result as a date). "SoSoExcelGuy" wrote: I am having trouble calculating the fiscal weeks of the current year. In my company's calendar, FW1 starts on Monday, January 2, 2006. I found the weeknum function and I keep having it tell me that FW 1 is on 1/1/06. Does anyone else know of other functions I can use or do I have to do some programming to get this straightened out? Added bonus question: If I dynamically link a cell that contains a date from MS Project to Excel, how can I format the cell in Excel to display the FW not the date. Any help with either of the questions would be greatly appreciated. |
All times are GMT +1. The time now is 08:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com