Excel Question

[foxbody-87]

Hello, I have set up an Excel spreadsheet with two tabs, the second tab is for data and the first tab presents it in a user-friendly format.

The data that goes into the second tab is copied and pasted from another spreadsheet (CSV format). I have noticed that this can also be achieved by using "Get Data" to open the browser and select the file I want to import from.

I would like to have a button somewhere on the first tab that has the function of bringing up the Get Data command - is this possible?

Any help appreciated. I'm using Excel 2016.

[djneils98]

record it as a macro and assign it to a button?

[foxbody-87]

I will give it a shot
Another question. The data I'm using presents the date as "20200329" as an example. I want to convert it to dd/mm/yy but when I format the cell it doesn't like it and gives me a string of #s. Is there a fix for this?

[Ultuous]

There may be a neater way but I always use the date() function.... =date(left(A1,4),mid(A1,5,2),right(A1,2)) should do it (A1 being the cell that you have original date string in)!

[foxbody-87]

OK guys this is working well so far!

Next query.

If I put a date in a cell, I'd like another cell to display the date of the following Thursday. So let's say it's Sunday the 29th March now and I type that in, the cell would display 02/04/20. Is that possible?

[Macneil]

Yes, just type =(cell ref+7), excel sees dates as a number I don't know the exact date, but it starts at 0 or 1 from tht fixed date

[Macneil]

foxbody-87 said:

I will give it a shot
Another question. The data I'm using presents the date as "20200329" as an example. I want to convert it to dd/mm/yy but when I format the cell it doesn't like it and gives me a string of #s. Is there a fix for this?

Silly question but have you widened the column to dispaly all of the contents?

[snuffy]

Macneil said:

foxbody-87 said:

I will give it a shot
Another question. The data I'm using presents the date as "20200329" as an example. I want to convert it to dd/mm/yy but when I format the cell it doesn't like it and gives me a string of #s. Is there a fix for this?

Silly question but have you widened the column to dispaly all of the contents?

Excel does indeed put ### if there's not enough space(width) for the value. However, if I put 20203029 into a cell and select "format as date" then it shows it will format it as "######", i.e. Excel does not recognise that example as a date.

Assuming your value is in A1, you could put =CONCAT(RIGHT(A1,2),"/",MID(A1,5,2),"/",MID(A1,3,2)) in B1 and it will display what you want.

[Ultuous]

foxbody-87 said:

If I put a date in a cell, I'd like another cell to display the date of the following Thursday. So let's say it's Sunday the 29th March now and I type that in, the cell would display 02/04/20. Is that possible?

A little messy, but =B1+7-WEEKDAY(B1,15) will do the trick (where B1 is previously 'fixed' date field)... This assumes that you're always seeking the 'closest' Thursday coming - e.g. 29/03 --> 02/04, 01/04 --> 02/04, 02/04 --> 02/04 too!

[Ultuous]

snuffy said:

Excel does indeed put ### if there's not enough space(width) for the value. However, if I put 20203029 into a cell and select "format as date" then it shows it will format it as "######", i.e. Excel does not recognise that example as a date.

Assuming your value is in A1, you could put =CONCAT(RIGHT(A1,2),"/",MID(A1,5,2),"/",MID(A1,3,2)) in B1 and it will display what you want.

That's certainly one way, but it effectively leaves the data stored as a string rather than date, which IME tends to leave problems further down the line (in fact it does with this one, as it makes it far harder to then find the following Thursday!)

[snuffy]

Ultuous said:

snuffy said:

Excel does indeed put ### if there's not enough space(width) for the value. However, if I put 20203029 into a cell and select "format as date" then it shows it will format it as "######", i.e. Excel does not recognise that example as a date.

Assuming your value is in A1, you could put =CONCAT(RIGHT(A1,2),"/",MID(A1,5,2),"/",MID(A1,3,2)) in B1 and it will display what you want.

That's certainly one way, but it effectively leaves the data stored as a string rather than date, which IME tends to leave problems further down the line (in fact it does with this one, as it makes it far harder to then find the following Thursday!)

Correct, it's not converted it to a date as you say,

But this work:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Now you can select the cell and pick the date format of your choosing !

[foxbody-87]

Ultuous said:

A little messy, but =B1+7-WEEKDAY(B1,15) will do the trick (where B1 is previously 'fixed' date field)... This assumes that you're always seeking the 'closest' Thursday coming - e.g. 29/03 --> 02/04, 01/04 --> 02/04, 02/04 --> 02/04 too!

Closest Thursday coming is right, thanks.
It's because our week ends on a Thursday and the sheet has to display the date the work was completed and the week ending date, will give that a try thanks.