MS Excel / VBA help thread

So if C1 is 544 you want to return 12899 in B1? Or do you want it to show =12355+544?

The latter. I've done it now with a bit of jiggery-pokery

I've probably overengineered it

You've not started to over-engineer it until your formula, at the very least, includes multiple nested if statements to validate the data and datatype in cell A1.

Real over-engineering would include purposefully circular references that fed back with auto-generated suggestions to correct the error.

https://youtu.be/N2QC6VQXo8U

I’ve inherited a file that’s read only and password protected. Anywhere that can brute force hack the password for free (company laptop has a duck ton of restrictions to install stuff)

Depends on the version of excel that it was protected under (assuming its an excel file of course).

Old versions, it's trivial. Anything else, and you're going to struggle.

I'd also be amazed if your company security policy allowed for files to be uploaded out of the company for any purpose, let alon cracking a PW.

Excel 2016.

Don’t fancy recreating this file from scratch. I’m screwed aren’t I…

Excel 2016 is tough.

There is this https://hashcat.net/hashcat/ which runs locally (no admin rights from what I can remember but not 100%) and can brute force passwords.

It isn't easy to use though and, unless the password is really short or you have an idea of the form it will take then it can take a long, long time. I left it running on a file for a week and it still hadn't solved it.

https://www.alphr.com/remove-password-excel-2016/
?

The sort of people who set Excel passwords usually use the year for the last 4 chars, so that should cut down the possible values...

Odd one.

Workbook 1 has date 1/1/2022

Xlookup in Workbook 2 returns the date as 1/1/2026

I've checked formatting etc. Why else would date be offset by 4 years?

Edit1: Additional. If I copy cell in wb1 and paste it in wb2 the same happens. Am I looking at date serial code "zero" issue across the two workbooks? I inherited wb1 and created wb2.

Edit2: ^ yes. Both workbooks date value is 44774

I think it's a Mac/Windows thing. There's a setting somewhere to switch between them (1904 date setting or similar).

Something to with this?
https://learn.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system

& @aggi It is indeed, huge thanks both. Ended up using the subtract 14xx days solution. Clunky.

Also called out my colleague as a Mac user, head of dept so hope it is taken in the jokey spirit intended else I might be clearing my desk later...

I hate dates in excel :(

Or rather dates exported from other software into excel. I'm not a poweruser :(

I don’t do dates

Don't get to excel much these days, have shifted to powerpoint plonker but got a solid 7 hours modelling in today and forgot how much its my happy place

How the fuck have I only just found out about:

=Cell=Cell

To quickly give you a TRUE/FALSE result?

I mean I normally copy my IF formula down the columns but this is so much quicker.

Wait till you do
=(cell = cell)+0

Multiply that by 1 and it's even better

Amazing!

Also

=(cell=cell)-0
and
=(cell=cell)/1

Seems to do the same thing.

Kinda wish I hadn't done thst as now I'm never going to remember which combo to do.

I’m producing this graph. And need to change the numbers along the vertical axis.

I want it to go up in units 1 hour or 1:00:00. But can’t figure out how. Any ideas

1 Attachment

Who doesn't hate working intime?
https://excelchamps.com/formulas/add-minutes-to-time/

set your first time to h:mm then in the next cell you want to use for your axisL
a1 = 11:00
a2 = a1 + (60/1440)

Or use the time function (do that) linked in the link above.

.

1 Attachment