Calculating the “month number” associated with a given “reporting period”

On the 7th workday of each month (in which holidays are taken into account), my company closes the previous month’s financial books. 
For example, the 7th workday in July 2011 for closing June 2011’s financial books is July 12.

During a given “reporting period” (which is defined as beginning on the 7th workday of the current month and ending on the 6th workday of the following month), I need to calculate the reporting period “month number” (i.e., 1=Jan, 2=Feb, 3=Mar, etc.). 
Using the example above, if the current date >= 7/12/2011 and < 8/9/2011 (i.e., the 7th workday in August), the calculated “month number” would be 7.

I am very familiar with the WEEKDAY and WORKDAY functions, and setting up a range of cells containing the dates of holidays. 
In fact, my first attempt at devising a formula resulted in the following formula:

=IF(NOW()=6,WORKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),7,Holidays_Lookup),WORKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),6,Holidays_Lookup)),MONTH(IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),2)>=6,WORKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),7,Holidays_Lookup),WORKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),6,Holidays_Lookup))-28),IF(AND(NOW()>=IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),2)>=6,WORKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),7,Holidays_Lookup),WORKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),6,Holidays_Lookup)),NOW()=6,WORKDAY(DATE(YEAR(NOW()),MONTH(NOW())+1,1),7,Holidays_Lookup),WORKDAY(DATE(YEAR(NOW()),MONTH(NOW())+1,1),6,Holidays_Lookup))),MONTH(IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),2)>=6,WORKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),7,Holidays_Lookup),WORKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),6,Holidays_Lookup))),MONTH(NOW())+1))

Note: Holidays_Lookup is a named range containing all the holidays that my company observes for the current and following years.

The previous formula does work, except when the current month is January.

Can anyone devise a less complex formula that consistently yields the correct “month number” associated with a given “reporting period”?

Any assistance would be greatly appreciated.
 Thanks.

Anwsers to the Problem Calculating the “month number” associated with a given “reporting period”

Download Error Fixer for Free Now

Again, the problems with aircode...
try the following:
 =IF((Month(workday(A1,-7,Holidays_Lookup))-1)>0,Month(workday(A1,-7,Holidays_Lookup))-1,12)
Note that the "-1" was originally missing on the TRUE element of the IF statement.
 
That takes care of everything except the exactly 7 days dates.
I suspect that what you are seeing is the effect of whether that date (the day) is included or excluded, analogous to the difference between < and <=
 
You'll have to decide which is technically accurate for your need, and if your original dates are the correct count, you could probably achieve the correct results by using -8 (or -6) instead of -7 to move the date range up or back a day.
Alternatively you
could play around with adding those 7th days to your holiday list, but that isn't as elegant as just shifting the number of days to match up with how Excel calculates dates.
 
HTH,
Keith

Tweak IE Options
  • Open Internet Explorer.
  • Go to "Tools > Internet Options > Advanced Tab".
  • Uncheck "Enable third-party browser extensions".
  • Click "Apply".
  • Reboot your computer.

Another Safe way to Fix the Problem: Calculating the “month number” associated with a given “reporting period”:

How to Fix Calculating the “month number” associated with a given “reporting period” with SmartPCFixer?

1. Download SmartPCFixer. Install it on your computer.

2. After the scan is done, you can see the errors and problems which need to be fixed.

3. The Fixing part is finished, the speed of your computer will be much higher than before and the errors have been removed.


Related: How to Update ASUS Xonar D2X driver for Windows XP 32/64-bit,Update ATI VGA Driver version 6.13.10.5779,How to Download Chicony Camera Driver version 61.005.029.1900,Download OKI C9800(PCL),How to Update TI Card Reader Driver version 2.0.0.10,Method to Download Toshiba Portege R835-P83 eco Utility v.1.3.21.0 driver,Method to Download Toshiba Satellite A660-BT2N23 Face Recognition v.3.1.3 driver,Download Toshiba Satellite L355-S7907 Face Recognition v.2.0.17.32 driver,[Solved] Download Toshiba Satellite L740-BT4N11 PC Health Monitor Utility v.1.7.9.64 driver,How to Download Toshiba Satellite P305D-S8828 WPS Library v.1.0.0.7 driver,Best Way to Download NVidia GeForce 6100 VGA Driver v.304.51 Certified,How Can You Update & Download NVidia GeForce 9300/nForce 730i VGA Driver v.310.19 Certified,Method to Download NVidia GeForce GT 330M Driver v.340.65,How Can I Update & Download NVidia GeForce GTX 590 Driver v.280.26 WHQL,Method to Download NVidia Tesla C2050 Driver v.319.17
Read More: How to Fix - Can I change the scope of a named range??,Troubleshoot:Can Flight Simulator 98 run on Windows 7? Error,Fast Solution to Error: Can formulas take into account the color of the cell on the spreadsheet?,How to Fix Error - can i convert a notepad file to excel?,Fast Solution to Error: Calculating pro-rated refunds,BSOD after upgraded to Windows 8.1 (memory problem?),Blurry programs on Windows 8.1,C:\Window|Microsoft.Net\Framework\v4.0.30319\mscoreei.dll could not be loaded,Browsing history is no longer visible,Bulk deleting from email deleted items folder
[PR]
by failedtoloadjn | 2016-08-26 15:19

Blank Screen After Boot Windows 7 Ultimate

I have windos 7 Ultimate recently i installed on my Laptop Lenovo T500, now iam facing a problem, when i bootup my laptop the starting window screen appear for some time after this the screen going to blank, then i press sleep function on  and function
off the screen will reapear in desktop, pls guide me what can i do for this problem.

Keys to the Problem Blank Screen After Boot Windows 7 Ultimate

Download Error Fixer (Free)

If you clean installed win7 did you then visit Lenovo site to install the drivers specific to your model?

Manually editing the Windows registry

Manually editing the Windows registry to remove invalid MACHINE_CHECK_EXCEPTION keys is not recommended unless you are PC service professional. Incorrectly editing your registry can stop your PC from functioning and create irreversible damage to your operating system. In fact, one misplaced comma can prevent your PC from booting entirely!


Caution: Unless you an advanced PC user, we DO NOT recommend editing the Windows registry manually. Using Registry Editor incorrectly can cause serious problems that may require you to reinstall Windows. We do not guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

To manually repair your Windows registry, first you need to create a backup by exporting a portion of the registry related to MACHINE_CHECK_EXCEPTION (eg. Windows Operating System):

  • Click the Start button.
  • Type "command" in the search box... DO NOT hit ENTER yet!
  • While holding CTRL-Shift on your keyboard, hit ENTER.
  • You will be prompted with a permission dialog box.
  • Click Yes.
  • A black box will open with a blinking cursor.
  • Type "regedit" and hit ENTER.
  • In the Registry Editor, select the Error 0x9C-related key (eg. Windows Operating System) you want to back up.
  • From the File menu, choose Export.
  • In the Save In list, select the folder where you want to save the Windows Operating System backup key.
  • In the File Name box, type a name for your backup file, such as "Windows Operating System Backup".
  • In the Export Range box, be sure that "Selected branch" is selected.
  • Click Save.
  • The file is then saved with a .reg file extension.
  • You now have a backup of your MACHINE_CHECK_EXCEPTION-related registry entry.

Recommended Method to Fix the Problem: Blank Screen After Boot Windows 7 Ultimate:

How to Fix Blank Screen After Boot Windows 7 Ultimate with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your computer.  Open it, and it will scan your computer. The errors will be shown in the scan result.

2. After the scan is done, you can see the errors and problems which need to be fixed.

3. The Fixing part is done, the speed of your computer will be much higher than before and the errors have been fixed.


Related: Better Option: Creating A Setup.exe Installer Or An MSI Installer?,Black Screen On Booting, No Windows Disc.,Battery Power Decreasing On My Laptop,Binding Data Source With Visual Studio 2008 Pro,Bar Chart - Count & Percentage
Read More: How Can I Fix - Battery won't charge after it reaches 80%?,[Solution] Bad Password error: Trouble Mapping Network Drive to Linux box using Windows 7,Solution to Error: Bcc in Window Mail,Troubleshooting:BLACK SCREEN AFTER WINDOWS XP PRO. LOGO UNABLE TO DO ANYTHING AFTER THAT!! Error,Troubleshooting:backup-restore is incompatable after upgrade from vista to w7,Bad_Pool_Caller Blue Screen on Windows 7 with SSD,Battery problem - can you help please.,Black screen problem in Windows 7,Black screen on boot up Windows 7 Home Premium,Bad Pool Caller error win8.1 Lenovo ThinkPad2
[PR]
by failedtoloadjn | 2016-08-10 17:31