Excel guru's - please help!

[airborne]

An excel workbook that I regularly use/update randomly changed the column used/formatted for date to currency on all the sheets and the pages aren't linked at all. I tried opening backups of the file and the same thing happens but it doesn't happen to other workbooks that have the identical setup. Quite a peculiar thing to happen, could possibly be some setting in Excel, it's the 2010 version.

Any reason why that could happen and how can I easily change/revert the column in question back to Date on all sheets?
Column 1 is used for date on all the affected sheets.

 

[A3@MBB]

Did you change any system time & language settings on the pc in question?

 

[airborne]

Did you change any system time & language settings on the pc in question?

No I haven't and I checked now and it all looks as it should be but I did notice that god damn irritating ENG language button has appeared in the task tray again so it could possibly be something there.
But even if something there had changed why would it only be affecting one Workbook and not all of them?

 

[Herr der Verboten]

I suppose the date is a plain number like 20220101 right? Either it's excel ****ing you around with regional settings/formatting, custom styles or cell formatting. Well that would be my guesses.

 

[mikef]

You could also check if the date format under the normal style has possibly changed for that particular worksheet - I seem to remember an Excel bug some years back that caused this - do not remember all the details.

 

[HAL 9000]

Check if your Windows Regional Settings are set to 'South Africa' and your language is set to 'English - South Africa' - then check the Additional Settings options to see if the decimal point option for both 'Number' & 'Currency' tabs is set to a 'full stop' and NOT a 'comma' and also that the list separator is set to a 'comma' and NOT a semicolon - so that Excel can import CSV files correctly.

Windows 11 Settings: - make sure you have CLOSED ALL of your Microsoft Office apps - ESPECIALLY Excel - before you do the following:

Go to Settings, then Time & language, then Language & region - check that they are set as detailed below:





Then open 'Control Panel', and open 'Region' - check that the formatting settings are as indicated below, then click on the 'Additional Settings' button.






Make sure that you set these highlighted two punctuation settings on BOTH the 'Number' AND the 'Currency' tabs as indicated, then click OK and close ALL open windows.

Now re-open Excel and then open your spreadsheet & check that the first column is displaying dates correctly.

To check if they are formatted as dates - press CTRL + ` (the tilde key just above TAB) to reveal formulas - and see if the dates now display as numbers - for example, today - 28 Dec 2022 - should display as 44923.



If you are still having issues, then DM me and I can remote connect to your PC via TeamViewer or AnyDesk and see if I can assist you further in resolving this issue.

 

[Geoff.D]

It would be an update that caused this. And it would be because that particular workbook is either an old one created on an earlier version, where the new version is now "converting" it to the new version and causing these changes. You will have to go through the workbook to fix it.

 

[CB40]

Check if your Windows Regional Settings are set to 'South Africa' and your language is set to 'English - South Africa' - then check the Additional Settings options to see if the decimal point option for both 'Number' & 'Currency' tabs is set to a 'full stop' and NOT a 'comma' and also that the list separator is set to a 'comma' and NOT a semicolon - so that Excel can import CSV files correctly.

Windows 11 Settings: - make sure you have CLOSED ALL of your Microsoft Office apps - ESPECIALLY Excel - before you do the following:

Go to Settings, then Time & language, then Language & region - check that they are set as detailed below:

View attachment 1448815

View attachment 1448819

Then open 'Control Panel', and open 'Region' - check that the formatting settings are as indicated below, then click on the 'Additional Settings' button.

View attachment 1448827


View attachment 1448833

Make sure that you set these highlighted two punctuation settings on BOTH the 'Number' AND the 'Currency' tabs as indicated, then click OK and close ALL open windows.

Now re-open Excel and then open your spreadsheet & check that the first column is displaying dates correctly.

To check if they are formatted as dates - press CTRL + ` (the tilde key just above TAB) to reveal formulas - and see if the dates now display as numbers - for example, today - 28 Dec 2022 - should display as 44923.

View attachment 1448837

If you are still having issues, then DM me and I can remote connect to your PC via TeamViewer or AnyDesk and see if I can assist you further in resolving this issue.

@Ivan Leon ,are you clued up up with macros in Excel?
I have some issues with the above but haven't a clue how it works

 

[HAL 9000]

I do Excel online training facilitation - Basic (L1) , Intermediate (L2) & Advanced (L3) levels - but that only covers the basics of macros - I leave advanced macro compiling to the Visual Basic for Applications (VBA) gurus... it's beyond my pay grade & skill level

 

[airborne]

I suppose the date is a plain number like 20220101 right? Either it's excel ****ing you around with regional settings/formatting, custom styles or cell formatting. Well that would be my guesses.

I always insert the date using the ctrl+ ; command which inserts the date and if I click on the cell and look at formula bar it shows as "21/12/2022" and then I've set it to display as 21 December 2022.

 

[WhiteRaven]

Not sure if I'm hijacking this thread or just expanding it... I need help with a formula, I'm creating a report that includes pulling the timesheet details for our admin team.

Basically I need the formula to look in the activity column for a specific activity and then calculate the time spent in the duration column.

e.g. look for "cv processing" in column B and calculate the duration in column K.

Any help will be super appreciated!

 

[Lostboy]

Use ChatGPT (if you have never used it before a simple Google it), type your excel problem and see what answer it gives you, may solve your problem for you.

Now I'm not saying the answer it will give is perfect, in most cases it is, but it should be able to atleast point you in the right direction regarding this and any other problem you may have.

 

[Lostboy]

Not sure if I'm hijacking this thread or just expanding it... I need help with a formula, I'm creating a report that includes pulling the timesheet details for our admin team.

Basically I need the formula to look in the activity column for a specific activity and then calculate the time spent in the duration column.

e.g. look for "cv processing" in column B and calculate the duration in column K.

Any help will be super appreciated!

ChatGPT, type your problem statement exactly as you mentioned here

 

[RedViking]

Not sure if I'm hijacking this thread or just expanding it... I need help with a formula, I'm creating a report that includes pulling the timesheet details for our admin team.

Basically I need the formula to look in the activity column for a specific activity and then calculate the time spent in the duration column.

e.g. look for "cv processing" in column B and calculate the duration in column K.

Any help will be super appreciated!

Have you tried Lookup function?

 

[richjdavies]

Not sure if I'm hijacking this thread or just expanding it... I need help with a formula, I'm creating a report that includes pulling the timesheet details for our admin team.

Basically I need the formula to look in the activity column for a specific activity and then calculate the time spent in the duration column.

e.g. look for "cv processing" in column B and calculate the duration in column K.

Any help will be super appreciated!

When you say "calculate the time spent". What calculation? Do you mean add up all the time already in column K -- then that sounds like a SUMIF or SUMIFS.
E.g. SUMIF(B:B, "CV processing", K:K)
or SUMIFS(K:K, B:B, "CV processing)... SUMIFS is the more modern approach and can add multiple criteria. Also I imagine you'll want to not type "CV processing explicitly but have a cell that contains it.

 

[richjdavies]

An excel workbook that I regularly use/update randomly changed the column used/formatted for date to currency on all the sheets and the pages aren't linked at all. I tried opening backups of the file and the same thing happens but it doesn't happen to other workbooks that have the identical setup. Quite a peculiar thing to happen, could possibly be some setting in Excel, it's the 2010 version.

Any reason why that could happen and how can I easily change/revert the column in question back to Date on all sheets?
Column 1 is used for date on all the affected sheets.

If you send a screenshot maybe it will be clearer, could either be a regional/language setting or a text to columns setting. Have seen similar things to above after someone opened a sheet on a Mac, and that person also had decimal formats in the "Afrikaans" format... (Decimal comma) and it messed up everything for months!

(Btw why are you using excel 2010... It's 2023 is it even still supported by Microsoft?!?

 

[maumau]

If you send a screenshot maybe it will be clearer, could either be a regional/language setting or a text to columns setting. Have seen similar things to above after someone opened a sheet on a Mac, and that person also had decimal formats in the "Afrikaans" format... (Decimal comma) and it messed up everything for months!

(Btw why are you using excel 2010... It's 2023 is it even still supported by Microsoft?!?

Think this is sorted, see post 3.

 

[maumau]

When you say "calculate the time spent". What calculation? Do you mean add up all the time already in column K -- then that sounds like a SUMIF or SUMIFS.
E.g. SUMIF(B:B, "CV processing", K:K)
or SUMIFS(K:K, B:B, "CV processing)... SUMIFS is the more modern approach and can add multiple criteria. Also I imagine you'll want to not type "CV processing explicitly but have a cell that contains it.

Also wondering what "calculate the time spent" means.

 

[RedViking]

Use ChatGPT (if you have never used it before a simple Google it), type your excel problem and see what answer it gives you, may solve your problem for you.

Now I'm not saying the answer it will give is perfect, in most cases it is, but it should be able to atleast point you in the right direction regarding this and any other problem you may have.

And, what answer does it return?

 

[WhiteRaven]

When you say "calculate the time spent". What calculation? Do you mean add up all the time already in column K -- then that sounds like a SUMIF or SUMIFS.
E.g. SUMIF(B:B, "CV processing", K:K)
or SUMIFS(K:K, B:B, "CV processing)... SUMIFS is the more modern approach and can add multiple criteria. Also I imagine you'll want to not type "CV processing explicitly but have a cell that contains it.

Thank you!!! This worked!! I couldn't get the sumif right, this really helped thanks so much