Microsoft Excel

[kphoger]

At least on my microwave, if you set it for 7:77, it automatically changes to 8:17 when you hit [Start].



Another common Excel date format issue for me is when it stores them as timestamps but only displays them as dates.  So, for example, I might have a dataset with 2000 lines.  Within that set are data relating to three different dates.  Then I go to filter the sheet by the "Date" column, only to find out that it only looks like dates.  It's actually 60 different timestamps:

3/30/2022 12:00:05 AM
3/30/2022 12:00:12 AM
3/30/2022 12:01:10 AM
3/30/2022 12:01:41 AM
3/31/2022 12:00:04 AM
3/31/2022 12:00:33 AM
3/31/2022 12:01:01 AM
etc.

This, apparently because the original export data that created the file I was sent also included times, and the data wasn't all generated at the exact same second each day.
Aarrgghh!

[GaryV]

Did you ever try to microwave something for 7 min 77 sec ( 7-7-7-start)?

I am often microwaving things for 90 seconds:

9 0 start

instead of

1 3 0 start

Or 8 - 8 - start or 9 - 9 - start - because then you're moving your finger one less time.

[kkt]

Did you ever try to microwave something for 7 min 77 sec ( 7-7-7-start)?

I am often microwaving things for 90 seconds:

9 0 start

instead of

1 3 0 start

Or 8 - 8 - start or 9 - 9 - start - because then you're moving your finger one less time.

Yes, exactly.  And on mine the display shows however you set the timer.  If you set the timer to 99 seconds, it'll display seconds only as it counts down.  If you set it to 1:09 it'll display minutes and seconds as it counts down.

[kphoger]

Or 8 - 8 - start or 9 - 9 - start - because then you're moving your finger one less time.

You certainly aren't the only one!

The numbers I punch into the microwave appear random to the uninitiated eye, but really I just try to keep the buttons close together. I'm much more likely to zap for 41 seconds than 40 seconds because the 1 is directly above the 4. Earlier today, I reheated a mug of tea for 71 seconds. After hitting Start, I was kicking myself because I realized I wouldn't have had to skip over any buttons if only I had entered it as 1:11 instead. Wow, this sure sounds really weird when I explain it in text.

I use one number on the microwave at a pop: :33, 2:22, :66, etc.  This is a habit I call laziness.

Me, too!

[kalvado]

So the whole number to date thing Excel forces is fun.

I get that if I put in "9-21" and it wants to change it to "21-Sep", OK, I get it.

When I put in "9-41" and it changes it to "41-Sep", I get a little pissed off.

Did you ever try to microwave something for 7 min 77 sec ( 7-7-7-start)?

I am often microwaving things for 90 seconds:

9 0 start

instead of

1 3 0 start

Joke making the rounds: What do Excel and incels have in common? They often think it's a date when it really isn't.

Yeah, Excel is a great quick and dirty tool for getting a general picture of data or a one-shot calculation.  But if you're using the data a lot or if it's really critical it's not a substitute for a database which will barf if you try to add numbers and words.

90 seconds is a legitimate timing, especially if other intervals in same batch are something like 30 and 60 seconds; or you deal with the pump doing 0.5 oz per second. But 7 min 77 sec is not a good representation. Two of  microwaves I used over time responded differently. One did 77 seconds and then 7 full minutes, the other instantly switched to 8.17
Better than 41st of september, though.

[kphoger]

Two of  microwaves I used over time responded differently. One did 77 seconds and then 7 full minutes, the other instantly switched to 8.17

So they did the same thing, then?

[kalvado]

Two of  microwaves I used over time responded differently. One did 77 seconds and then 7 full minutes, the other instantly switched to 8.17

So they did the same thing, then?

They both accepted the input and ran for same amount of time. But displays were different.
I wonder if more modern smarter ones would give out a error in such case, though.

[J N Winkler]

I am often microwaving things for 90 seconds:

9 0 start

instead of

1 3 0 start

I often use a stove timer that works similarly (except with hours and minutes rather than minutes and seconds).  However, if I enter "9 0," it flashes "Err" briefly (to tell me I should be ashamed of myself) before it starts displaying the time remaining as "1:30."

[kphoger]

I don't see how a microwave would accept 90 seconds but not 7:77.  A better challenge would be to try microwaving something for 99:61.

In the interest of science, I just tried it here at work:

:90 → changes to 1:30 when I hit [Start]
98:70 → changes to 99:10 when I hit [Start]
99:99 → no change, just starts counting down to 99:98 then 99:97 et seq.

[Scott5114]

So the whole number to date thing Excel forces is fun.

I get that if I put in "9-21" and it wants to change it to "21-Sep", OK, I get it.

When I put in "9-41" and it changes it to "41-Sep", I get a little pissed off.

Did you ever try to microwave something for 7 min 77 sec ( 7-7-7-start)?

I am often microwaving things for 90 seconds:

9 0 start

instead of

1 3 0 start

On my microwave, I'd hit the "+30 seconds" button 3 times, since it automatically starts when that button is pressed, so that also saves me a keystroke over entering any time 2:00 or less.

[kphoger]

On my microwave, I'd hit the "+30 seconds" button 3 times, since it automatically starts when that button is pressed, so that also saves me a keystroke over entering any time 2:00 or less.

Simply pressing [1] on mine starts it for one minute, and [Start] adds 30 seconds.  So I actually press [1] [Start] in order to heat something for 90 seconds.

[Scott5114]

On my microwave, I'd hit the "+30 seconds" button 3 times, since it automatically starts when that button is pressed, so that also saves me a keystroke over entering any time 2:00 or less.

Simply pressing [1] on mine starts it for one minute, and [Start] adds 30 seconds.  So I actually press [1] [Start] in order to heat something for 90 seconds.

I used to have a microwave like that (1-6 were all labeled with "quick start" and would start the microwave for 1-6 minutes) but I hated it because I tend to have an allergic reaction to non-orthogonality. There was an "enter time" button you could press to bypass it, but I feel like I should be able to freely enter time by default and any quick-start buttons should be separate functions.

[Rothman]

At least on my microwave, if you set it for 7:77, it automatically changes to 8:17 when you hit [Start].



Another common Excel date format issue for me is when it stores them as timestamps but only displays them as dates.  So, for example, I might have a dataset with 2000 lines.  Within that set are data relating to three different dates.  Then I go to filter the sheet by the "Date" column, only to find out that it only looks like dates.  It's actually 60 different timestamps:

3/30/2022 12:00:05 AM
3/30/2022 12:00:12 AM
3/30/2022 12:01:10 AM
3/30/2022 12:01:41 AM
3/31/2022 12:00:04 AM
3/31/2022 12:00:33 AM
3/31/2022 12:01:01 AM
etc.

This, apparently because the original export data that created the file I was sent also included times, and the data wasn't all generated at the exact same second each day.
Aarrgghh!

=LEFT...

Done.

Quit your whining.

[kalvado]

On my microwave, I'd hit the "+30 seconds" button 3 times, since it automatically starts when that button is pressed, so that also saves me a keystroke over entering any time 2:00 or less.

Simply pressing [1] on mine starts it for one minute, and [Start] adds 30 seconds.  So I actually press [1] [Start] in order to heat something for 90 seconds.

I used to have a microwave like that (1-6 were all labeled with "quick start" and would start the microwave for 1-6 minutes) but I hated it because I tend to have an allergic reaction to non-orthogonality. There was an "enter time" button you could press to bypass it, but I feel like I should be able to freely enter time by default and any quick-start buttons should be separate functions.

really depends on how fine control you want. If 30 seconds is the minimum step you need, those quick buttons make things easy. Stopping the run few seconds early is another option.

[kphoger]

Another common Excel date format issue for me is when it stores them as timestamps but only displays them as dates.  So, for example, I might have a dataset with 2000 lines.  Within that set are data relating to three different dates.  Then I go to filter the sheet by the "Date" column, only to find out that it only looks like dates.  It's actually 60 different timestamps:

3/30/2022 12:00:05 AM
3/30/2022 12:00:12 AM
3/30/2022 12:01:10 AM
3/30/2022 12:01:41 AM
3/31/2022 12:00:04 AM
3/31/2022 12:00:33 AM
3/31/2022 12:01:01 AM
etc.

This, apparently because the original export data that created the file I was sent also included times, and the data wasn't all generated at the exact same second each day.
Aarrgghh!

=LEFT...

Done.

Quit your whining.

I just tried that by using =LEFT(A2,5) but can't seem to get the resultant number back into date format.  It just sticks there as a 5-digit number.

However, what does appear to work is using =INT(A2).

[abefroman329]

Another common Excel date format issue for me is when it stores them as timestamps but only displays them as dates.  So, for example, I might have a dataset with 2000 lines.  Within that set are data relating to three different dates.  Then I go to filter the sheet by the "Date" column, only to find out that it only looks like dates.  It's actually 60 different timestamps:

3/30/2022 12:00:05 AM
3/30/2022 12:00:12 AM
3/30/2022 12:01:10 AM
3/30/2022 12:01:41 AM
3/31/2022 12:00:04 AM
3/31/2022 12:00:33 AM
3/31/2022 12:01:01 AM
etc.

This, apparently because the original export data that created the file I was sent also included times, and the data wasn't all generated at the exact same second each day.
Aarrgghh!

=LEFT...

Done.

Quit your whining.

I just tried that by using =LEFT(A2,5) but can't seem to get the resultant number back into date format.  It just sticks there as a 5-digit number.

However, what does appear to work is using =INT(A2).

Text to Columns, uncheck the box next to Tab on step 2, select Text on step 3.  From there, you can use =LEFT(A2,5) to get the month, day, and year, and then convert those five digits to dates.

You're welcome.

[kphoger]

Or just do =INT(A2).   

[Rothman]

Or just do =INT(A2).   

Heh.

[Dirt Roads]

Or just do =INT(A2).   

Going full circle, you could just do what one of my old bosses always wanted us to do  dump the output from Excel into a CSV file (or TXT file) to clean up the data.   

[kkt]

So the whole number to date thing Excel forces is fun.

I get that if I put in "9-21" and it wants to change it to "21-Sep", OK, I get it.

When I put in "9-41" and it changes it to "41-Sep", I get a little pissed off.

Did you ever try to microwave something for 7 min 77 sec ( 7-7-7-start)?

I am often microwaving things for 90 seconds:

9 0 start

instead of

1 3 0 start

On my microwave, I'd hit the "+30 seconds" button 3 times, since it automatically starts when that button is pressed, so that also saves me a keystroke over entering any time 2:00 or less.

I don't have a +30 seconds button.  I have a +1 minute button, though.

[MikeTheActuary]

Hey fellas, if I shouldn't be using Excel to edit CSVs, what should I be using?

emacs 

[kphoger]

One thing about people saying to use a proper database instead...

My Excel files are not just export data:  they're also import data.  For example, I export data from an MSO's live online database (accessed via VPN) in Excel format, then I do all my data manipulation with sorting and formulas and pivot tables and all that jazz.  But then I take the final product and upload it into our own company's payroll WMS (created in-house).  So, the data initially comes to me in Excel format, and then I need it to be in Excel format at the end of the process as well.

[abefroman329]

One thing about people saying to use a proper database instead...

My Excel files are not just export data:  they're also import data.  For example, I export data from an MSO's live online database (accessed via VPN) in Excel format, then I do all my data manipulation with sorting and formulas and pivot tables and all that jazz.  But then I take the final product and upload it into our own company's payroll WMS (created in-house).  So, the data initially comes to me in Excel format, and then I need it to be in Excel format at the end of the process as well.

Yeah.

Another thing about people saying to use a proper database instead: I don't know how to use Access, my company isn't going to pay for Access training, and neither am I.  And SQL would be a total nonstarter, as I barely know how to use it, yet that would make me the SQL expert at my company.

[JayhawkCO]

SQL isn't hard in and of itself. There's basically only 10 commands or so. It's how to make it actually return the info you want that's the challenge.

[kkt]

Hey fellas, if I shouldn't be using Excel to edit CSVs, what should I be using?

emacs