Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 38927 times)

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6358
  • Total likes: 69
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #810 on: April 02, 2017, 12:23:20 PM »
No difference imho. You're better off in access and linking your excel to the access DB.
Access uses less memory?

Offline CZ60

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Jul 2014
  • Posts: 382
  • Total likes: 22
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #811 on: April 02, 2017, 12:27:02 PM »
Access uses less memory?
Can handle a lot more rows of data, up to 2gb, and won't crash. You can connect to an external data source in Excel and have it look at the db file.

Offline Mikeoracle

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2008
  • Posts: 1714
  • Total likes: 3
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #812 on: April 02, 2017, 12:44:14 PM »
You can also try checking the box "add data to model" in the pivot wizard, that may help with processing large amounts of data.

Offline stooges44

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jan 2017
  • Posts: 2875
  • Total likes: 100
  • DansDeals.com Hat Tips 110
    • View Profile
Re: Excel Problem
« Reply #813 on: April 19, 2017, 11:39:34 AM »
If I have this in a cell: 6:55 - 7:00 PM , what formula can I use to give me: 7:00 - 7:05 PM in the adjacent cell?

Thanks

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 3353
  • Total likes: 56
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Excel Problem
« Reply #814 on: April 19, 2017, 01:03:54 PM »
If I have this in a cell: 6:55 - 7:00 PM , what formula can I use to give me: 7:00 - 7:05 PM in the adjacent cell?

Thanks
This will add 5 minutes to a Time cell.
Code: [Select]
=A1+5/1440


But, being that you have 2 Times in 1 cell (probably formatted as General or Text) you'll need to re-build that part of your workbook to make it work.
Once the game is over, the king and the pawn go back in the same box.

Offline stooges44

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jan 2017
  • Posts: 2875
  • Total likes: 100
  • DansDeals.com Hat Tips 110
    • View Profile
Re: Excel Problem
« Reply #815 on: April 19, 2017, 01:07:55 PM »
This will add 5 minutes to a Time cell.
Code: [Select]
=A1+5/1440


But, being that you have 2 Times in 1 cell (probably formatted as General or Text) you'll need to re-build that part of your workbook to make it work.

I know, I can do =A1+TIME(0,5,0) to also give me 5 minutes but like you said I have 2 times in a cell and I was hoping to get one large formula for everything.

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 3353
  • Total likes: 56
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Excel Problem
« Reply #816 on: April 19, 2017, 01:26:15 PM »
I know, I can do =A1+TIME(0,5,0) to also give me 5 minutes but like you said I have 2 times in a cell and I was hoping to get one large formula for everything.
Don't think that's possible. Good luck!
Once the game is over, the king and the pawn go back in the same box.

Online etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8375
  • Total likes: 111
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #817 on: April 19, 2017, 01:36:32 PM »
it would be much easier if you could split the times into 2 cells
Workflowy. You won't know what you're missing until you try it.

Offline stooges44

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jan 2017
  • Posts: 2875
  • Total likes: 100
  • DansDeals.com Hat Tips 110
    • View Profile
Re: Excel Problem
« Reply #818 on: April 19, 2017, 01:48:58 PM »
it would be much easier if you could split the times into 2 cells

Of course, but that would be the as much work as not using a formula  ;)

I've gotten it theory but it gives the result in decimal format, not as H:MM PM

In cell L13 I have 6:55 - 7:00 PM, and I managed to make this formula

Code: [Select]
=CONCAT(LEFT(L13, SEARCH(" ",L13,1))+TIME(0,5,0)," - ", RIGHT(L13, SEARCH(" ",L13,1)+2)+TIME(0,5,0))
which essentially finds the 2 times, adds 5 to each and then joins them with the "-" but the result is in decimal format like this: 0.291666666666667 - 0.795138888888889 even after I change the cell type to time it has no affect

Offline CreamofSoup

  • Dansdeals Gold Elite
  • ***
  • Join Date: May 2016
  • Posts: 212
  • Total likes: 11
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Baltimore
Re: Excel Problem
« Reply #819 on: April 19, 2017, 02:12:10 PM »
Try:
Code: [Select]
=CONCAT(text(LEFT(L13, SEARCH(" ",L13,1))+TIME(0,5,0),"H:MM AM/PM")," - ", text(RIGHT(L13, SEARCH(" ",L13,1)+2)+TIME(0,5,0),"H:MM AM/PM"))
The Text function before the left and right (and the H:MM AM/PM) formatting after the decimal number will force the decimal to the time that it represents, I think

Of course, but that would be the as much work as not using a formula  ;)

I've gotten it theory but it gives the result in decimal format, not as H:MM PM

In cell L13 I have 6:55 - 7:00 PM, and I managed to make this formula

Code: [Select]
=CONCAT(LEFT(L13, SEARCH(" ",L13,1))+TIME(0,5,0)," - ", RIGHT(L13, SEARCH(" ",L13,1)+2)+TIME(0,5,0))
which essentially finds the 2 times, adds 5 to each and then joins them with the "-" but the result is in decimal format like this: 0.291666666666667 - 0.795138888888889 even after I change the cell type to time it has no affect

Wasn't sure I had everything in the right place but this will work.
Code: [Select]
=CONCATENATE(TEXT(LEFT(L13, SEARCH(" ",L13,1))+TIME(0,5,0),"H:MM")," - ", TEXT(RIGHT(L13, SEARCH(" ",L13,1)+2)+TIME(0,5,0),"H:MM AM/PM"))
« Last Edit: April 19, 2017, 02:19:26 PM by CreamofSoup »

Offline stooges44

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jan 2017
  • Posts: 2875
  • Total likes: 100
  • DansDeals.com Hat Tips 110
    • View Profile
Re: Excel Problem
« Reply #820 on: April 19, 2017, 02:19:37 PM »
Try:
Code: [Select]
=CONCAT(text(LEFT(L13, SEARCH(" ",L13,1))+TIME(0,5,0),"H:MM AM/PM")," - ", text(RIGHT(L13, SEARCH(" ",L13,1)+2)+TIME(0,5,0),"H:MM AM/PM"))
The Text function before the left and right (and the H:MM AM/PM) formatting after the decimal number will force the decimal to the time that it represents, I think

Creamofsoup you are a cream of genius! that worked perfectly! thanks so much!

Offline lechatchileh ariber

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Mar 2013
  • Posts: 889
  • Total likes: 24
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #821 on: April 23, 2017, 02:25:29 AM »
Using GAS
I'm copying values to range, using get last row+1.
The values which I am copying sometimes have one row of Data, sometimes 2 rows of Data and sometimes 3.
I put getRange (row, 1, 3)

My issue is that for the next submission it goes to the fourth next line even if the 2nd and 3rd line had no data. (because it's copying a blank cell into the target sheet)

Any ideas?
I don't sin, I give myself opportunities to repent.

Offline CZ60

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Jul 2014
  • Posts: 382
  • Total likes: 22
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #822 on: April 23, 2017, 11:14:15 AM »

Offline lechatchileh ariber

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Mar 2013
  • Posts: 889
  • Total likes: 24
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #823 on: April 23, 2017, 11:45:32 AM »
Whats GAS?
Google apps script
Google sheets version of VBA/macros (correct terminology?)
Runs based on JavaScript
I don't sin, I give myself opportunities to repent.

Offline lechatchileh ariber

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Mar 2013
  • Posts: 889
  • Total likes: 24
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #824 on: April 23, 2017, 03:41:33 PM »
Using GAS
I'm copying values to range, using get last row+1.
The values which I am copying sometimes have one row of Data, sometimes 2 rows of Data and sometimes 3.
I put getRange (row, 1, 3)

My issue is that for the next submission it goes to the fourth next line even if the 2nd and 3rd line had no data. (because it's copying a blank cell into the target sheet)

Any ideas?
Eta: fixed using
If (small > number) {getRange...
I don't sin, I give myself opportunities to repent.