Continue Excel to New Uear

[Tightrope6969]

What is the best way to continue workbook to new year keeping formulas and formatting, but not values?

 

[Doc.AElstein]

Hello

Your post reads a bit open ended. I mean it is a bit open to interpretation what you are asking.
The simple answer is that an Excel workbook by default has no dependencies on which year it is in. So the simple answer is just carry on as you where in the previous year.

If there is something in the workbook that may be effected by the year, then obviously that might be a different story.

I don’t think there is any point in trying to help you further unless you can explain in more detail what your question is, and to what specifically it applies. Otherwise a lot of time would be wasted trying to guess what your question actually is.

As I mentioned, a workbook itself usually has no reason to be year dependant.

Alan

 

[Tightrope6969]

Hello

Your post reads a bit open ended. I mean it is a bit open to interpretation what you are asking.
The simple answer is that an Excel workbook by default has no dependencies on which year it is in. So the simple answer is just carry on as you where in the previous year.

If there is something in the workbook that may be effected by the year, then obviously that might be a different story.

I don’t think there is any point in trying to help you further unless you can explain in more detail what your question is, and to what specifically it applies. Otherwise a lot of time would be wasted trying to guess what your question actually is.

As I mentioned, a workbook itself usually has no reason to be year dependant.

Alan

I own a tractor trailer. I keep up with weekly, monthly and yearle income and expenses. At the end of the year, I use the totals for tax purposes so I have to total everything and start over, however, the dates for the weeks in a month are not lining up. Due to this, the formulas don't seem to be accurate either. Hope this clarifies some?

 

[Doc.AElstein]

....... Hope this clarifies some?

Hi
It does give some clarity, thanks. But not enough to be able to help you much more.
It’s a bit like you trying to get help from a mechanic by phoning a garage and saying your tractor is not pulling as well as last year and asking what is the best way to get it to work as well as it did last year. In fact it’s much worse: Because every workbook is different, so it’s like there are no vehicle manufacturers and everybody builds their own vehicles in different forms and then you try the same again with a phone call to a garage. Even a very competent mechanic would only be able to give you some very simple suggestions, or general background information, that probably would not take you much further.

---

Some general background info
Doing anything in Excel involving dates will almost always cause problems eventually.
This is because there are so many different ways and conventions to write a date, especially in shortened number format.
Excel does its best to guess what date or time you mean when you write a date or time, but occasionally it mixes things up.
It’s very common for example for Excel to mix up the numbers for a day and the numbers for a month. It may even miss read the number for a year and take it as a day and month. It is also not uncommon for Excel to mistake a date for a time and visa versa.

To go some way to try to minimise these problems, Excel itself handles dates internally deep down in its workings in a particular way: Excel sees a date as a simple number
Today ( Thursday 12th January, 2023 ) is in Excel’s eyes seen as the whole number 44938
Tomorrow, Friday 13th is 44939 as far as Excel is concerned

Fractions give us the time.
0.5 is midday. So 12 o’clock, midday today, ( Midday, Thursday 12th January, 2023 ) is seen by Excel as 44938.5

Excel is terrible at converting what you give it to the number it wants to use internally, and Excel is just as bad as converting that number back into the date and time you want to see. Hence at some time or other you will most likely get some problems. This often goes unnoticed, or is ignored: Example: Our Town is not too big, and often I see official Letters with the wrong date written on them. They probably don’t have such a good computer expert and he uses Microsoft office to produce their official letters. The Post Office is a much bigger organisation so are more likely to have computer people and other software that get it correct. Often in official letters they say “date as on post mark” instead, as they can’t rely on their date, and its much more likely that the post mark is correct. !


If, at midday today I write in a cell in my Excel the formula
=Now()
, then I will see this
01-12-2023 12:00

If other people do the same in their Excel, a few may see exactly the same as me, but more likely they will see it a bit differently. This is because Excel will try to guess how you prefer to see it, based on your internal settings, anything you have done before with times and dates, what country you are in, what excel version you have, etc. etc…

If you and me start swapping workbooks with anything to do with dates in it then we will likely get in a total mess with dates and times being changed left right and centre.
That’s Excel in the spreadsheet
Excel VBA is slightly better, since we can do all our calculations using that number which always represents a specific date and time. In addition we can use coding to control, at least to some extent, what anyone will see in a cell when we share the workbook.

If you are just using the default Excel spreadsheet with formulas, then you are at Excel’s mercy, and relying on it trying to guess correctly 2 things
_ how you like to see things
_ what date and time you mean when you type it in.

---

How to go forward in getting help at a (free) help forum
A couple of possibilities,

_1) Upload your workbook. ( You may want to change any personal data: Change any names and addresses to fictional ones and change any data you don’t want anyone to see). Try and explain as best you can what the problem is: What values are wrong and, importantly, say what they should be.
I doubt you will get any help then here, as there are not so many Excel people passing by just now.

There is only a small chance you will get help at one of the major forums. They do have hundreds of people eager to answer many questions every day, but they mostly prefer to answer as many quick short questions that they can: They seem to be motivated like children in a classroom chasing their post count, or merit star likes, or what ever. But you might get lucky. Someone may take a particular interests or as there are more people passing there, someone may have coincidentally done something very similar, so rather than solve your problem they will give you a complete working solution alternative.

_2) You will improve your chances of getting help at any free forum if you try to isolate a typical problem example , and post all the details about that, in particular again saying what you are getting that is wrong, and what the correct result should be.
Take a look at any forum question that gets an answer. For example the last one I gave here: https://www.pcreview.co.uk/threads/...-time-to-another-sheet.4077153/#post-14289911
https://www.pcreview.co.uk/threads/...-time-to-another-sheet.4077153/#post-14289916
A simple example there with clear explanation of what was wanted. I had no idea how to solve that. No one can know all the vast amount of things that can be done with Excel, and especially with VBA coding, which is almost anything.

(Of course on a paid forum or other paid service it’s a totally different story. It probably won’t cost much to fix the problem, but someone will happy spend a few days going back and forth with you to get more details and clarity, and happily charge you for it!)

I am like a hobby Excel mechanic, as are most forum voluntary helpers: Give me something in the hand that is broken, and when / if I have the time and interest, I can try a to fix it.

Alan

 

[Tightrope6969]

Thank you for your reply and assistance. I will redact my book and upload it in the near future. Thanks again, Rick. (e-mail address removed)

 

[Tightrope6969]

I am not sure how to upload the workbook I get the message "The upload file does not have allowed extension. The following are allowed: zip, .txt,.png,.jpg,.jpe,.gif. please advise. Thanks.

 

[Doc.AElstein]

Hi,

I am new to this forum, so I don’t know too much about how the workbook uploading is organised here.

You could try to zip (compress) the file, and upload the zipped (compressed) file.
( If you zip your file, you would be able to upload typically a bigger file, so zipping is probably a good idea anyway. )
If you are not too familiar with how to zip your file, there is plenty of info on the internet to show you.
For example: https://support.microsoft.com/en-us/windows/zip-and-unzip-files-8d28fa72-f2f9-712f-67df-f80cf89fd4e5

Alternatively you could use a file sharing site. Upload your file there and pass us the share link.
( I use app box ( free ) , but there are many others.
https://www.box.com/pricing/individual
https://i.postimg.cc/T3n616pq/app-box-free.jpg )



Alan

 

[benalt]

You mentioned you are dealing with dates. Perhaps, you could hard code the first date as 1/1/2023, for example in A1, then if the data were going down the column, you could put in cell A2 =$A1+1 and repeat that all the way down the column for each day. If you were dealing with the first day in each month you could use =eomonth($A1,0)+1 etc. The following year you would just need to change the date in A1 to set up the workbook for the new year.

 

[tallboy]

What is the best way to continue workbook to new year keeping formulas and formatting, but not values?

I've had success with columns of dates by selecting the whole column, format as date, using YYYY-MM-DD format, and then entering the current date as 12jan, for example, and Excel will save it in correct format for the current year in the column. For the new year, I right-click the tab, choose copy, make copy in current book and just change the tab name to the new year. Then delete the old dates and data entries, but keep the formulas. Now this new tab can be used for the new year. You could do the same by saving the old file with the new year name, do the formatting as described above, delete the old dates and data, save, and it should be ready.

 

[benalt]

If you want to clear mulitple varying cells in sheets automatically, you could insert a module and do something like this using VBA:
sub resetSheet() ' Clear contents from cells C1, D2:D4 and C5 Worksheets("Sheet1").Range("C1,D2:D4,C5").ClearContents End Sub