Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Need help with an excel formula or function.
New Posts  All Forums:Forum Nav:

Need help with an excel formula or function.

post #1 of 6
Thread Starter 
Hey all. I'm trying to create a column of dates in Excel but the normal auto-fill won't do. I need the dates to insert, skip a cell, insert the same date, skip a cell, then go to the next date, skip a cell, etc. It needs to come out like this:


I would manually do it but I need it to go from 1-1-2006 all the way to the present and it would take me weeks to do. I know there is a way to do this automatically, I just simply don't know how. A solution or a point in the right direction would be awesome.

Thanks all.
Edited by PhillyOverclocker - 11/6/13 at 7:28am
My System
(17 items)
 
  
CPUMotherboardGraphicsGraphics
AMD Phenom II X4 955  Gigabyte GA-MA790XT-UD4P Sapphire HD 6950 Sapphire HD 6950 
RAMHard DriveHard DriveOptical Drive
G-Skill Mushkin Callisto Deluxe WD Black LG Supermulti 
CoolingOSKeyboardPower
Rasa Black CPU water block. Rasa X20 pump/res c... Windows 7 Ultimate x64 Deck Legend Fire mechanical with Cherry Black s... Cooler Master Silent Pro Gold 1200W modular PSU. 
CaseMouseAudio
Cooler Master Storm Scout Logitech MX-518 Logitech Wireless headset for gaming and 100w M... 
  hide details  
Reply
My System
(17 items)
 
  
CPUMotherboardGraphicsGraphics
AMD Phenom II X4 955  Gigabyte GA-MA790XT-UD4P Sapphire HD 6950 Sapphire HD 6950 
RAMHard DriveHard DriveOptical Drive
G-Skill Mushkin Callisto Deluxe WD Black LG Supermulti 
CoolingOSKeyboardPower
Rasa Black CPU water block. Rasa X20 pump/res c... Windows 7 Ultimate x64 Deck Legend Fire mechanical with Cherry Black s... Cooler Master Silent Pro Gold 1200W modular PSU. 
CaseMouseAudio
Cooler Master Storm Scout Logitech MX-518 Logitech Wireless headset for gaming and 100w M... 
  hide details  
Reply
post #2 of 6
Fill Handle.
My System
(15 items)
 
  
CPUMotherboardGraphicsRAM
FX6300 Black M5A99X EVO R2.0 Nvidia GTS450 Team Vulcan PC3 12800 
Hard DriveOptical DriveCoolingOS
Samsung 840 PRO Asus DRW-1608P (x2) Custom Water Cooling Win7 (Ult), Win 8.1 & Win Server 2012 R2 
MonitorKeyboardPowerCase
2 X Samsung 915N Ducky Shine III, Blue Cherry/Blue LEDs PCP&C 1kw Lian Li PC-71 (W/Window) 
MouseAudio
Logiteck G400s none 
  hide details  
Reply
My System
(15 items)
 
  
CPUMotherboardGraphicsRAM
FX6300 Black M5A99X EVO R2.0 Nvidia GTS450 Team Vulcan PC3 12800 
Hard DriveOptical DriveCoolingOS
Samsung 840 PRO Asus DRW-1608P (x2) Custom Water Cooling Win7 (Ult), Win 8.1 & Win Server 2012 R2 
MonitorKeyboardPowerCase
2 X Samsung 915N Ducky Shine III, Blue Cherry/Blue LEDs PCP&C 1kw Lian Li PC-71 (W/Window) 
MouseAudio
Logiteck G400s none 
  hide details  
Reply
post #3 of 6
Has this been answered yet?

Oh well. Just type in the first date, highlight the date and one cell below and grab the fill handle and drag down. Like this: -

For Gaming
(18 items)
 
For Working
(8 items)
 
For Serving
(8 items)
 
CPUMotherboardGraphicsRAM
AMD FX-8350 @ 4.6 Ghz ASUS M5A97-PRO Sapphire R9 290X Tri-X 4GB @ 1170/1475 8GB Corsair Vengeance Blue LP DDR3 2133 Mhz 
Hard DriveHard DriveOptical DriveCooling
Crucial M4 SSD 128 GB Toshiba SATA III 2TB SAMSUNG DVDRW Corsair H60 
OSMonitorKeyboardPower
Windows 10 Pro 64 Bit Asus MG279Q 27" FreeSync WQHD 144hz IPS Corsair RGB K65 Cherry Red Corsair CX750M 
CaseMouseAudioAudio
Cooler Master Elite 430 Corsair Vengeance M65 Alesis iO2 Express Audio Interface Wharfedale Pro Diamond 8.2 Active Studio Monitors 
Audio
Corsair Vengeance 1500 v2 7.1 Dolby Headset 
CPUGraphicsRAMHard Drive
Intel Core i7-3740QM CPU @ 2.70 Ghz NVidia Quadro K1000M 2GB 32GB DDR3 500GB 7200RPM 
Optical DriveOSMonitorOther
Slot DVDRW Windows 7 64-Bit 1080p Built in Display Dell Precision M4700 
  hide details  
Reply
For Gaming
(18 items)
 
For Working
(8 items)
 
For Serving
(8 items)
 
CPUMotherboardGraphicsRAM
AMD FX-8350 @ 4.6 Ghz ASUS M5A97-PRO Sapphire R9 290X Tri-X 4GB @ 1170/1475 8GB Corsair Vengeance Blue LP DDR3 2133 Mhz 
Hard DriveHard DriveOptical DriveCooling
Crucial M4 SSD 128 GB Toshiba SATA III 2TB SAMSUNG DVDRW Corsair H60 
OSMonitorKeyboardPower
Windows 10 Pro 64 Bit Asus MG279Q 27" FreeSync WQHD 144hz IPS Corsair RGB K65 Cherry Red Corsair CX750M 
CaseMouseAudioAudio
Cooler Master Elite 430 Corsair Vengeance M65 Alesis iO2 Express Audio Interface Wharfedale Pro Diamond 8.2 Active Studio Monitors 
Audio
Corsair Vengeance 1500 v2 7.1 Dolby Headset 
CPUGraphicsRAMHard Drive
Intel Core i7-3740QM CPU @ 2.70 Ghz NVidia Quadro K1000M 2GB 32GB DDR3 500GB 7200RPM 
Optical DriveOSMonitorOther
Slot DVDRW Windows 7 64-Bit 1080p Built in Display Dell Precision M4700 
  hide details  
Reply
post #4 of 6
Thread Starter 
Quote:
Originally Posted by JoeChamberlain View Post

Has this been answered yet?

Oh well. Just type in the first date, highlight the date and one cell below and grab the fill handle and drag down. Like this: -


Thanks for the response.

Yeah it wasn't quite that easy. If all the cells were to be sequential I could have done it no problem at all but if you see my first post I had to double each date with spaces in between. Excel simply won't auto-fill like that. Eventually what I had to do was make the sequential dates I needed (like you showed), copy them below the first ones (essentially making the same column of dates twice), then I sorted the column so each date was written twice each going down the column. Then I had to take a column of blank cells and alternate them into the column so it would come out the way I showed in my original post. I eventually got it and it's actually working out like a charm.

BTW, this data was then used as part of an Access script for automatically compacting and repairing databases. It took me a few days to write the full script but it saves me a few hours a week repairing old corrupted databases manually. Here's a sample. The full script is thousands of lines:
Code:
if exist C:\OrderHist\1-1-2007.mdb (GOTO :EXISTING) ELSE GOTO :MISSING
:EXISTING
"C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe" "C:\OrderHist\1-1-2007.mdb" /compact
:MISSING
if exist C:\OrderHist\1-2-2007.mdb (GOTO :EXISTING) ELSE GOTO :MISSING
:EXISTING
"C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe" "C:\OrderHist\1-2-2007.mdb" /compact
:MISSING
if exist C:\OrderHist\1-3-2007.mdb (GOTO :EXISTING) ELSE GOTO :MISSING
:EXISTING
"C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe" "C:\OrderHist\1-3-2007.mdb" /compact
:MISSING
if exist C:\OrderHist\1-4-2007.mdb (GOTO :EXISTING) ELSE GOTO :MISSING
:EXISTING
"C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe" "C:\OrderHist\1-4-2007.mdb" /compact
:MISSING

etc.. etc.. etc..
My System
(17 items)
 
  
CPUMotherboardGraphicsGraphics
AMD Phenom II X4 955  Gigabyte GA-MA790XT-UD4P Sapphire HD 6950 Sapphire HD 6950 
RAMHard DriveHard DriveOptical Drive
G-Skill Mushkin Callisto Deluxe WD Black LG Supermulti 
CoolingOSKeyboardPower
Rasa Black CPU water block. Rasa X20 pump/res c... Windows 7 Ultimate x64 Deck Legend Fire mechanical with Cherry Black s... Cooler Master Silent Pro Gold 1200W modular PSU. 
CaseMouseAudio
Cooler Master Storm Scout Logitech MX-518 Logitech Wireless headset for gaming and 100w M... 
  hide details  
Reply
My System
(17 items)
 
  
CPUMotherboardGraphicsGraphics
AMD Phenom II X4 955  Gigabyte GA-MA790XT-UD4P Sapphire HD 6950 Sapphire HD 6950 
RAMHard DriveHard DriveOptical Drive
G-Skill Mushkin Callisto Deluxe WD Black LG Supermulti 
CoolingOSKeyboardPower
Rasa Black CPU water block. Rasa X20 pump/res c... Windows 7 Ultimate x64 Deck Legend Fire mechanical with Cherry Black s... Cooler Master Silent Pro Gold 1200W modular PSU. 
CaseMouseAudio
Cooler Master Storm Scout Logitech MX-518 Logitech Wireless headset for gaming and 100w M... 
  hide details  
Reply
post #5 of 6
Wow OK. That's some work to do. No, I didn't read the post carefully.

Could you not have done the script in a loop?
For Gaming
(18 items)
 
For Working
(8 items)
 
For Serving
(8 items)
 
CPUMotherboardGraphicsRAM
AMD FX-8350 @ 4.6 Ghz ASUS M5A97-PRO Sapphire R9 290X Tri-X 4GB @ 1170/1475 8GB Corsair Vengeance Blue LP DDR3 2133 Mhz 
Hard DriveHard DriveOptical DriveCooling
Crucial M4 SSD 128 GB Toshiba SATA III 2TB SAMSUNG DVDRW Corsair H60 
OSMonitorKeyboardPower
Windows 10 Pro 64 Bit Asus MG279Q 27" FreeSync WQHD 144hz IPS Corsair RGB K65 Cherry Red Corsair CX750M 
CaseMouseAudioAudio
Cooler Master Elite 430 Corsair Vengeance M65 Alesis iO2 Express Audio Interface Wharfedale Pro Diamond 8.2 Active Studio Monitors 
Audio
Corsair Vengeance 1500 v2 7.1 Dolby Headset 
CPUGraphicsRAMHard Drive
Intel Core i7-3740QM CPU @ 2.70 Ghz NVidia Quadro K1000M 2GB 32GB DDR3 500GB 7200RPM 
Optical DriveOSMonitorOther
Slot DVDRW Windows 7 64-Bit 1080p Built in Display Dell Precision M4700 
  hide details  
Reply
For Gaming
(18 items)
 
For Working
(8 items)
 
For Serving
(8 items)
 
CPUMotherboardGraphicsRAM
AMD FX-8350 @ 4.6 Ghz ASUS M5A97-PRO Sapphire R9 290X Tri-X 4GB @ 1170/1475 8GB Corsair Vengeance Blue LP DDR3 2133 Mhz 
Hard DriveHard DriveOptical DriveCooling
Crucial M4 SSD 128 GB Toshiba SATA III 2TB SAMSUNG DVDRW Corsair H60 
OSMonitorKeyboardPower
Windows 10 Pro 64 Bit Asus MG279Q 27" FreeSync WQHD 144hz IPS Corsair RGB K65 Cherry Red Corsair CX750M 
CaseMouseAudioAudio
Cooler Master Elite 430 Corsair Vengeance M65 Alesis iO2 Express Audio Interface Wharfedale Pro Diamond 8.2 Active Studio Monitors 
Audio
Corsair Vengeance 1500 v2 7.1 Dolby Headset 
CPUGraphicsRAMHard Drive
Intel Core i7-3740QM CPU @ 2.70 Ghz NVidia Quadro K1000M 2GB 32GB DDR3 500GB 7200RPM 
Optical DriveOSMonitorOther
Slot DVDRW Windows 7 64-Bit 1080p Built in Display Dell Precision M4700 
  hide details  
Reply
post #6 of 6
Thread Starter 
Quote:
Originally Posted by JoeChamberlain View Post

Wow OK. That's some work to do. No, I didn't read the post carefully.

Could you not have done the script in a loop?

I tried to use a loop but Access can only open one named file at a time. If it comes to a line in the batch file that doesn't correlate to an existing file it stops. So this was the best I could do. It's actually working right now while I write this compacting a folder containing over 1000 .accdb and .mdb files, all with different names.

EDIT: It just got finished running and turned a 9.5GB file full of databases into just over 1GB. After I do this I run a conversion tool to convert the files to SQL and where it used to take a day or two it now takes a couple of hours, which is something I wasn't expecting. So instead of saving me a few hours a week this is going to save me a day or 2 each week. biggrin.gif
Edited by PhillyOverclocker - 11/14/13 at 10:07am
My System
(17 items)
 
  
CPUMotherboardGraphicsGraphics
AMD Phenom II X4 955  Gigabyte GA-MA790XT-UD4P Sapphire HD 6950 Sapphire HD 6950 
RAMHard DriveHard DriveOptical Drive
G-Skill Mushkin Callisto Deluxe WD Black LG Supermulti 
CoolingOSKeyboardPower
Rasa Black CPU water block. Rasa X20 pump/res c... Windows 7 Ultimate x64 Deck Legend Fire mechanical with Cherry Black s... Cooler Master Silent Pro Gold 1200W modular PSU. 
CaseMouseAudio
Cooler Master Storm Scout Logitech MX-518 Logitech Wireless headset for gaming and 100w M... 
  hide details  
Reply
My System
(17 items)
 
  
CPUMotherboardGraphicsGraphics
AMD Phenom II X4 955  Gigabyte GA-MA790XT-UD4P Sapphire HD 6950 Sapphire HD 6950 
RAMHard DriveHard DriveOptical Drive
G-Skill Mushkin Callisto Deluxe WD Black LG Supermulti 
CoolingOSKeyboardPower
Rasa Black CPU water block. Rasa X20 pump/res c... Windows 7 Ultimate x64 Deck Legend Fire mechanical with Cherry Black s... Cooler Master Silent Pro Gold 1200W modular PSU. 
CaseMouseAudio
Cooler Master Storm Scout Logitech MX-518 Logitech Wireless headset for gaming and 100w M... 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Need help with an excel formula or function.