Show Overnight Data as Occuring on One Day

[bjk896]

I have data from an outposted data logger that logs date and time of an occurrence eg 2021-09-28_19-15-07 for Date and hh-mm-ss. This data only occurs between 1700 and 0700 next day, so for one night’s effort I get Date 1 for occurrences to midnight and Date 2 for those post midnight.

2021-09-28_19-15-07 shows for 28/9/2021 and
2021-09-29_03-37-16 shows for 29/2/2021

I don’t care which day is used when it is all finally put on a scatter chart but it is more meaningful for this to be shown as 1 day.
I have no problem converting formats between time and date serial and numbers

How do I do it ?

I then make a scatter chart using date serials as numbers and decimal times (with X = Date, Y = Time) How can I eliminate the redundant times between 0700 and 1700 in the Time axis?

bjk

 

[ajitexcel]

Hello there, how many occurrences will you have for one day i.e. between 1700 and 0700? like you have two logs in your example.

Is this what you are looking for as in image

 

[bjk896]

Hello there, how many occurrences will you have for one day i.e. between 1700 and 0700? like you have two logs in your example.

Is this what you are looking for as in image

Thanks for the interest -It can vary from one or two up to a thousand. We're talking microbats - night travellers. The resultant scatterchart would tell me over a short or long number of days the dominant hours they show and hence there can be an association with other questions down the track eg temp and humidity - another story.

 

[bjk896]

Thanks for the interest -It can vary from one or two up to a thousand. We're talking microbats - night travellers. The resultant scatterchart would tell me over a short or long number of days the dominant hours they show and hence there can be an association with other questions down the track eg temp and humidity - another story.

ajitel - I guess I am too involved to explain myself properly The attached image outlines the problem with some sample data with the correct time/date. The issue is getting rid of that gap between 0700 and 1700 and making the read of the data such that there is continuity in the hours

How do I attach the file itelf so you can see my data ?xb12bb looked too complex to me first up
bjk

 

[ajitexcel]

can share in dropbox

 

[bjk896]

can share in dropbox

I dont have dropbox or any cloud storage

 

[bebo021999]

Still do not understant what you are trying to do, but let make some twists:

Book1
	A	B	C	D
3	Original Date-Time stamp	Offset back to be on same day	Date only	Time only
4	2021-09-28_17-00-00	28/09/2021 14:00:00	28/09/2021	14:00:00
5	2021-09-28_18-00-00	28/09/2021 15:00:00	28/09/2021	15:00:00
6	2021-09-28_19-00-00	28/09/2021 16:00:00	28/09/2021	16:00:00
7	2021-09-28_20-00-00	28/09/2021 17:00:00	28/09/2021	17:00:00
8	2021-09-28_21-00-00	28/09/2021 18:00:00	28/09/2021	18:00:00
9	2021-09-28_22-00-00	28/09/2021 19:00:00	28/09/2021	19:00:00
10	2021-09-28_23-00-00	28/09/2021 20:00:00	28/09/2021	20:00:00
11	2021-09-28_23-59-00	28/09/2021 20:59:00	28/09/2021	20:59:00
12	2021-09-29_01-00-00	28/09/2021 22:00:00	28/09/2021	22:00:00
13	2021-09-29_02-00-00	28/09/2021 23:00:00	28/09/2021	23:00:00
14	2021-09-29_03-00-00	28/09/2021 23:59:36	28/09/2021	23:59:36
Sheet5

Cell Formulas
Range	Formula
B4:B13	B4	=DATEVALUE(LEFT(A4,10))+TIMEVALUE(SUBSTITUTE(RIGHT(A4,8),"-",":"))-TIMEVALUE(SUBSTITUTE(RIGHT($A$14,8),"-",":"))
C4:C14	C4	=INT(B4)
D4:D14	D4	=MOD(B4,1)
B14	B14	=DATEVALUE(LEFT(A14,10))+TIMEVALUE(SUBSTITUTE(RIGHT(A14,8),"-",":"))-TIMEVALUE(SUBSTITUTE(RIGHT($A$14,8),"-",":"))-1/3600

 

[bjk896]

Sorry for the confusion but thank you for staying with me
I think I have x12bb OK now so here is the test sheet I'd been playing with including two charts which illustrate the problem
The task is to take one night's data - 28th hours and 29th hours -and make it appear as one day - 29th and cut out the daylight gap hours.. I have one chart showing the "as logged" data as it would display. The second chart has the data all inclusive to one day but not indicating sequential hours as well as a gap from 0800-1600 which is daylight. I'd like to make that look as a continuous 1700-0700 chart - no gaps

I hope this makes it a bit clearer and more usefull
If the world is kind the formulas used should be included

BJK Long File Name Charting.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
4	2021-09-28_17-00-00.zc	2021	09	28	17	00	00	17:00:00	28/09/2021	17		44466	0.708333	5pm			17:00	5PM	44467	0.708333
5	2021-09-28_18-00-00.zc	2021	09	28	18	00	00	18:00:00	28/09/2021	18		44466	0.75	6pm			18:00		44467	0.75
6	2021-09-28_19-00-00.zc	2021	09	28	19	00	00	19:00:00	28/09/2021	19		44466	0.791667	7pm			19:00		44467	0.791667
7	2021-09-28_20-00-00.zc	2021	09	28	20	00	00	20:00:00	28/09/2021	20		44466	0.833333	8pm			20:00		44467	0.833333
8	2021-09-28_21-00-00.zc	2021	09	28	21	00	00	21:00:00	28/09/2021	21		44466	0.875	9pm			21:00		44467	0.875
9	2021-09-28_22-00-00.zc	2021	09	28	22	00	00	22:00:00	28/09/2021	22		44466	0.916667	10PM			22:00		44467	0.916667
10	2021-09-28_23-00-00.zc	2021	09	28	23	00	00	23:00:00	28/09/2021	23		44466	0.958333	11PM			23:00		44467	0.958333
11	2021-09-28_23-59-00.zc	2021	09	28	23	59	00	23:59:00	28/09/2021	23		44466	0.999306	12Mn			0:00		44467	0.999306
12	2021-09-28_01-00-00.zc	2021	09	28	01	00	00	01:00:00	28/09/2021	1		44467	0.041667	1am			1:00		44467	0.041667
13	2021-09-28_02-00-00.zc	2021	09	28	02	00	00	02:00:00	28/09/2021	2		44467	0.083333	2am			2:00		44467	0.083333
14	2021-09-28_03-00-00.zc	2021	09	28	03	00	00	03:00:00	28/09/2021	3		44467	0.125	3am			3:00		44467	0.125
15	2021-09-28_04-00-00.zc	2021	09	28	04	00	00	04:00:00	28/09/2021	4		44467	0.166667	4am			4:00		44467	0.166667
16	2021-09-28_05-00-00.zc	2021	09	28	05	00	00	05:00:00	28/09/2021	5		44467	0.208333	5am			5:00		44467	0.208333
17	2021-09-28_06-00-00.zc	2021	09	28	06	00	00	06:00:00	28/09/2021	6		44467	0.25	6am			6:00		44467	0.25
18	2021-09-28_07-00-00.zc	2021	09	28	07	00	00	07:00:00	28/09/2021	7		44467	0.291667	7am			7:00		44467	0.291667
19
Sheet1

Cell Formulas
Range	Formula
B4:B18	B4	=MID(A4,1,4)
C4:C18	C4	=MID(A4,6,2)
D4:D18	D4	=MID(A4,9,2)
E4:E18	E4	=MID(A4,12,2)
F4:F18	F4	=MID(A4,15,2)
G4:G18	G4	=MID(A4,18,2)
H4:H18	H4	=CONCATENATE(E4,":",F4,":",G4)
I4:I18	I4	=CONCATENATE(D4,"/",C4,"/",B4)
M4:M18,J4:J18	J4	=VALUE(E4)
L4:L18	L4	=+ IF(VALUE(J4)>16,S4-1,S4)
S4:S18	S4	=VALUE(I4)
T4:T18	T4	=VALUE(H4)

 

[bjk896]

the charts didnt convert as well so here

[

 

[bjk896]

bebo02199 Been offline for a while - I'm not familiar with the ----(SUBSTITUTE(RIGHT($A$14,8) ---- component of your formulae for B Column

Could you fill me in a bit please bjk