• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Renaming of days in a spreadsheet

J

jenwren

New Member
Hi bit of a humdinger of a problem here. I oversee a project that runs shifts on a Friday and Saturday night from 21.00hrs to 03.00hrs. I have information on who visited the project for every shift logged in a spreadsheet with part of the columns logging day, date and time.


The day is automatically added in from the date so is logged as Friday, Saturday or Sunday.


I need to 'rename' the days so I know whether the information was logged on a 'Friday' night shift - which would be from 21.00hrs on a Friday to 03.00hrs on a Saturday. Or whether it was a 'Saturday' night shift - which would be from 21.00hrs on a Saturday to 03.00hrs on a Sunday. Obviously the problem is that if it was past midnight on Friday it's logged as Saturday with the change in date, though it was a Friday night shift etc.


This information is logged from 22nd November 2008 and I need a yearly count of visitors depending on whether the visited on a Friday night shift or a Saturday night shift so need to be able to rename the days in the first column accordingly.

I've copied a bit of the information below to give you an idea. Hoping that description makes sense, can anyone wrap their brain round it? Here's hopin - thank you!

[pre]
Code: 
Friday	07/12/2012	23.45	00.20
Friday	07/12/2012	23.20	01.15
Saturday08/12/2012	00.01	00.09
Saturday08/12/2012	23.30	23.42
Saturday08/12/2012	23.15	23.30
Saturday08/12/2012	23.00	23.10
Sunday	09/12/2012	01.30	01.48
Sunday	09/12/2012	01.15	01.25
Sunday	09/12/2012	01.20	02.10
Sunday	09/12/2012	01.40	02.00
Sunday	09/12/2012	01.10	01.25
Sunday	09/12/2012	00.55	01.10
Sunday	09/12/2012	00.40	02.45
Sunday	09/12/2012	00.47	00.55
Sunday	09/12/2012	22.30	22.40
Sunday	09/12/2012	02.20	03.00
Friday	14/12/2012	02.35	02.50
Friday	14/12/2012	21.45	22.10
Saturday15/12/2012	02.02	02,28
Saturday15/12/2012	23.30	23.45
Saturday15/12/2012	00.00	00.40
Saturday15/12/2012	22.45	23.00
Saturday15/12/2012	02.15	02.25
Saturday15/12/2012	02.45	03.25
Sunday	16/12/2012	02.10	02.25
Sunday	16/12/2012	00.20	01.10
Sunday	16/12/2012	00.45	00.59
[/pre]
 
just to summarize a bit... you basically want to be able to 'return' "friday" if the log was made during the Friday night shift? so if the log/record was created on Sat. at 1:30am, you want it to see it as friday?
 
To add to Jason's question, for this entry

Code: 
Saturday15/12/2012	02.45	03.25

Is this a Friday night shift, or Saturday morning (since the 2 times overlap)?
 
If we just go off of the first time stamp, one simple idea would be to simply subtract 3 hrs. This formula adds in some controls to convert the bits and pieces into a date/time value, then does that.

=TEXT(B2+TIMEVALUE(SUBSTITUTE(TEXT(C2,"0.00"),".",":"))-3/24,"dddd")
 
Hi Jason and Luke,


Yes, if it was 1.30am on a Saturday it was actually a Friday night shift and if it was 2.45am on a Saturday night shift it would have been logged as Sunday.


Hope that clarifies a bit.


Thank you for your help.
 
Now I'm really confused...why would this:

Code: 
Saturday15/12/2012	02.45	03.25

need to be changed to Sunday shift?


Perhaps you could add to your example above and show what your desired result would be?
 
WWHHAAAAAAT!?! LUKE IS CONFUSED!?!!?! thats it!! a sign of the END OF DAYS!! I'm going into hiding in my underground nuclear safe bunker!!!


good luck to all of yoU!
 
....while im down here (echoing)... [edited/ nevermind, i found the answer to my question in your first post]


can you clean up your data example from your first post (i cant view uploaded sheets due to work security restrictions...darn Secret Agency lol)?? and perhaps the 'desired' day you'd want returned?


for now:

perhaps your sample data is completely made upl, but the dates you have dont match the day of the week next to it. EX: "Friday --- 12/07/12" Excel returns this as 12/7/12 being a Saturday.
 
Why not add another column for Shift date

Use a Formula like:

=IF(AND(C2<1,C2>0.125),B2,IF(AND(C2>=0,C2<0.125),B2-1,B2))

Where Column C is the Start Time of the activity

Column B is the Original Date

0.125 is 3:00 am in Excel talk

0.0 is midnight
 
@jason

Sadly, mind reading is not one of my many skills.

For the example data, if you paste it into XL and then use Text-to-Columns with fixed widths to split things up. Do note that the dates use dd/mm/yy formats. Or, I could clean it for you...

[pre]
Code: 
Friday	        07-Dec-2012	23.45	0.2
Friday	        07-Dec-2012	23.2	1.15
Saturday	08-Dec-2012	0.01	0.09
Saturday	08-Dec-2012	23.3	23.42
Saturday	08-Dec-2012	23.15	23.3
Saturday	08-Dec-2012	23	23.1
Sunday        	09-Dec-2012	1.3	1.48
Sunday        	09-Dec-2012	1.15	1.25
Sunday        	09-Dec-2012	1.2	2.1
Sunday        	09-Dec-2012	1.4	2
Sunday	        09-Dec-2012	1.1	1.25
Sunday	        09-Dec-2012	0.55	1.1
Sunday	        09-Dec-2012	0.4	2.45
Sunday	        09-Dec-2012	0.47	0.55
Sunday        	09-Dec-2012	22.3	22.4
Sunday        	09-Dec-2012	2.2	3
Friday	        14-Dec-2012	2.35	2.5
Friday	        14-Dec-2012	21.45	22.1
Saturday	15-Dec-2012	2.02	2.28
Saturday	15-Dec-2012	23.3	23.45
Saturday	15-Dec-2012	0	0.4
Saturday	15-Dec-2012	22.45	23
Saturday	15-Dec-2012	2.15	2.25
Saturday	15-Dec-2012	2.45	3.25
Sunday	        16-Dec-2012	2.1	2.25
Sunday	        16-Dec-2012	0.2	1.1
Sunday	        16-Dec-2012	0.45	0.59
[/pre]
 
I'd also suggest that you enter dates in the proper format as hh:mm:ss

eg:

[pre]
Code: 
Friday		7/12/2012	23:27:00	0:12:00
Friday		7/12/2012	23:12:00	1:09:00
Saturday	8/12/2012	0:00:00		0:05:00
Saturday	8/12/2012	23:18:00	23:25:00
Saturday	8/12/2012	23:09:00	23:18:00
Saturday	8/12/2012	23:00:00	23:06:00
Sunday		9/12/2012	1:18:00		1:28:00
Sunday		9/12/2012	1:09:00		1:15:00
Sunday		9/12/2012	1:12:00		2:06:00
Sunday		9/12/2012	1:24:00		2:00:00
Sunday		9/12/2012	1:06:00		1:15:00
Sunday		9/12/2012	0:33:00		1:06:00
Sunday		9/12/2012	0:24:00		2:27:00
Sunday		9/12/2012	0:28:00		0:33:00
Sunday		9/12/2012	22:18:00	22:24:00
Sunday		9/12/2012	2:12:00		3:00:00
Friday		14/12/2012	2:21:00		2:30:00
Friday		14/12/2012	21:27:00	22:06:00
Saturday	15/12/2012	2:01:00		2:16:00
Saturday	15/12/2012	23:18:00	23:27:00
Saturday	15/12/2012	0:00:00		0:24:00
Saturday	15/12/2012	22:27:00	23:00:00
Saturday	15/12/2012	2:09:00		2:15:00
Saturday	15/12/2012	2:27:00		3:15:00
Sunday		16/12/2012	2:06:00		2:15:00
Sunday		16/12/2012	0:12:00		1:06:00
Sunday		16/12/2012	0:27:00		0:35:00
[/pre]
 
i've done that...but im questioning the second row.... it shows as 4:48am to 3:36am; which would make it 1a +-23hr period... just want to confirm this rather than assume and then end up with a useless formula.


Hui: thats what im trying to setup/workout
 
You haven't told us what format the data is in


So I have assumed 23.2 = 23 Hrs and .2 Hr? = 23:12:00 HH:MM:SS?
 
But your second rows is 23.2 which is still in the same day ?

My formula gives me:

[pre]
Code: 
Friday		7/12/2012	23:27:00	00:12:00	7/12/2012
Friday		7/12/2012	23:12:00	01:09:00	7/12/2012
Saturday	8/12/2012	00:00:00	00:05:00	7/12/2012
Saturday	8/12/2012	23:18:00	23:25:00	8/12/2012
Saturday	8/12/2012	23:09:00	23:18:00	8/12/2012
Saturday	8/12/2012	23:00:00	23:06:00	8/12/2012
Sunday		9/12/2012	01:18:00	01:28:00	8/12/2012
Sunday		9/12/2012	01:09:00	01:15:00	8/12/2012
Sunday		9/12/2012	01:12:00	02:06:00	8/12/2012
Sunday		9/12/2012	01:24:00	02:00:00	8/12/2012
Sunday		9/12/2012	01:06:00	01:15:00	8/12/2012
Sunday		9/12/2012	00:33:00	01:06:00	8/12/2012
Sunday		9/12/2012	00:24:00	02:27:00	8/12/2012
Sunday		9/12/2012	00:28:00	00:33:00	8/12/2012
Sunday		9/12/2012	22:18:00	22:24:00	9/12/2012
Sunday		9/12/2012	02:12:00	03:00:00	8/12/2012
Friday		14/12/2012	02:21:00	02:30:00	13/12/2012
Friday		14/12/2012	21:27:00	22:06:00	14/12/2012
Saturday	15/12/2012	02:01:00	02:16:00	14/12/2012
Saturday	15/12/2012	23:18:00	23:27:00	15/12/2012
Saturday	15/12/2012	00:00:00	00:24:00	14/12/2012
Saturday	15/12/2012	22:27:00	23:00:00	15/12/2012
Saturday	15/12/2012	02:09:00	02:15:00	14/12/2012
Saturday	15/12/2012	02:27:00	03:15:00	14/12/2012
Sunday		16/12/2012	02:06:00	02:15:00	15/12/2012
Sunday		16/12/2012	00:12:00	01:06:00	15/12/2012
Sunday		16/12/2012	00:27:00	00:35:00	15/12/2012
[/pre]
 
and in Lukes example above

Saturday 15/12/2012 02.45 03.25

Shouldn't it be changed to a Friday shift as it starts before 3am on Saturday ?
 
You must log in or register to reply here.
Back
Top