 | Home > Off Topic > MSO - excel spreadsheet experts |
 
|
|
|
| Caterham Member Since: 06 Nov 2008 Location: Birmingham Posts: 6346   
|
hey looking for some help...as ever.
got a spread sheet I'm trying to extract some info from. first column is a date / time tag in this format.... 01/05/2015 00:30 01/05/2015 01:00 01/05/2015 01:30 above is all in column A. I'm trying to extract values from column B where column A = Monday to Friday 7am till 7pm? possible? thanks for looking. |
||
10th Jun 2015 2:22pm |
|
| Paulv8 Member Since: 09 Jun 2013 Location: Milton Keynes Posts: 618 
|
yep. Will have a look later if nobody else does it for you first V8 90...WIP
|
||
|
10th Jun 2015 2:36pm |
|
| Caterham Member Since: 06 Nov 2008 Location: Birmingham Posts: 6346
|
brill - thanks Paul
forgot to say this is going to be a monthly submission which I'll need to do each week so I need to format the spread sheet in such a manner that I can paste new values in each month....? |
||
|
10th Jun 2015 2:37pm |
|
| Paulv8 Member Since: 09 Jun 2013 Location: Milton Keynes Posts: 618
|
This will give you a zero for the column if the time is 7pm to 6.59am V8 90...WIP
|
||
|
10th Jun 2015 2:45pm |
|
| Paulv8 Member Since: 09 Jun 2013 Location: Milton Keynes Posts: 618
|
This can all be in one equation, but I have done it like this to simplify, there are also other ways to do it, but again, this is easy to follow. The Excel police will be along in a minute to tell me off because there are special functions for exactly this task, V8 90...WIP
|
||
|
10th Jun 2015 2:49pm |
|
| Caterham Member Since: 06 Nov 2008 Location: Birmingham Posts: 6346
|
Paul,
thanks ever so much. just out of a meeting. please don't do anymore on this for me. I'll go through what you've suggested and what I've seen in the meeting - will drop an update later. thanks again. |
||
|
10th Jun 2015 3:46pm |
|
| TheWarden Member Since: 25 Feb 2013 Location: Southampton Posts: 179  
|
If the date/time is in column A, and the value is in column B
Column C "=TEXT((A1),"ddd")" Column D "=TIME(HOUR(A1),,)" Column E "=IF(AND(OR(C1="Mon",C1="Tue",C1="Wed",C1="Thu",C1="Fri"),AND(D1>6/24),AND(D1<20/24)),B2,0)" If you put these all in row 1 and copy them by select the cell and dragging down, the A1,C1, etc in the formulas will autoupdate to the correct row values Enjoy 2013 Aintree Green 110 Station Wagon SX |
||
|
10th Jun 2015 4:41pm |
|
| Paulv8 Member Since: 09 Jun 2013 Location: Milton Keynes Posts: 618
|
haha I forgot the day of the week bit - It would be easier to use the weekday function though imho V8 90...WIP
|
||
|
10th Jun 2015 5:02pm |
|
| Paulv8 Member Since: 09 Jun 2013 Location: Milton Keynes Posts: 618
|
column a =date
column b = amount column c =hour(a2) column d =weekday(a2) and actually now matter what I do - this website refuses to accept the code string in this box.....it keeps editing it so it comes out wrong. so I attach an image of the correct string! for column E...except the last C2 should be B2 (sorry bout that!)  V8 90...WIP |
||
|
10th Jun 2015 5:12pm |
|
| Caterham Member Since: 06 Nov 2008 Location: Birmingham Posts: 6346
|
flippin hecks guys....I've a lot to learn with this excel malarkey ! following my meeting we've looked at copying the time stamp column a couple of times, then formatting the column to give days rather than dates then applying filters....I suspect this is a simpler but more long winded approach to the formula you've suggested which IF I can make it work will be far better / simpler in the long run (I'll give it a go). my next task will be to see if I can import a column from one excel file into another, I think I can recall how to do that from one tag in the same workbook into another tag but not from another file? once again |
||
|
18th Jun 2015 10:46am |
|
| Caterham Member Since: 06 Nov 2008 Location: Birmingham Posts: 6346
|
wow. it worked. ....
I just need to swap the formula around as I need to collate the values for 19hrs through to 06.59hrs but reckon I can do that ....then on to the days of the week ......hmmm. thanks again. |
||
|
18th Jun 2015 11:12am |
|
| Caterham Member Since: 06 Nov 2008 Location: Birmingham Posts: 6346
|
OOPS struggling again here;
06/04/2015 15:00 5 Slave 90 - Point 2 Mon 15:00 Mon Mon 3:00 PM 6 first column being the date stamp column B being the value required last column is using the following formula; =IF(AND(OR(G1="MON",G1="TUE",G1="WED",G1="THU",G1="FRI"),AND(H1>6),AND(H1<19)),B2,0) Am I right in thinking this should provide a "0" in the last column for anything outside Mon to Fri 7am to 7pm? (which is what I'm after). whats most peculiar to me is that 5 (column B) has become 6 which has happened to in several instances - some (not all) values are either higher or lower? |
||
|
18th Jun 2015 1:35pm |
|
| Caterham Member Since: 06 Nov 2008 Location: Birmingham Posts: 6346
|
worked it out......to a degree.
I was getting a 30min time lapse ie the answer was for the row above. so all sorted except for I've got the values for 7am till 7pm....just need to work out how to get 7pm till 7am? - hopefully be easy |
||
|
18th Jun 2015 2:16pm |
|
| Eduardo Member Since: 28 Aug 2008 Location: Región Metropolitana Posts: 2110  
|
Caterham
An easy one in only one cell Suppose that your date stamp is in the B2 cell Click image to enlarge Sorry but PhP make strange things when I put the formula in the post First "IF" check if the day are between 2(monday) and 6(friday) if is true the value is 1 Second "IF" check if the hour is between 7 to 19 hrs in is true the value is 1 By multipliying both "IF" condititions all the time stamps that are between Monday and Friday between 7 to 19 hrs will give you avalue of 1, if not 0. So both conditions sorted out 
Regards Eduardo MY 2007 110 SW PUMA 2.4: Big Fog of 64' MY 1994 Jayco 1207 Folding camper: "El Tremendo" Click image to enlarge |
||
|
18th Jun 2015 3:42pm |
|
|
|
| All times are GMT + 1 Hour |
< Previous Topic | Next Topic > |
Posting Rules
|
Site Copyright © 2006-2025 Futuranet Ltd & Martin Lewis
