Combining separate day and time columns...

nihilan

New Member
Joined
Mar 16, 2004
Messages
4
Ok, I have a two part question here:

My data is set up with 5 columns:

Day (Julian) Hour Minute Frequency Signal Strength


First I want to find a way to collapse the Day, hour and minute into one column.

Second, I would like to plot the signal strength against the time for each of 20 frequencies (there are about 11,000 rows in this data set – each is a recording of one of 20 transmitters, the time it occurred and the strength of the signal). However, normally when you plot this sort of graph in excel the times with 0 values do not show up. I want an x axis that shows the entire time scale of interest (say the month of February) even if there are only 5 data points for that month.

Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

GaryB

Active Member
Joined
Feb 25, 2002
Messages
459
Welcome to the board!

if your date is in A2, your hour in B2 and your minutes in C2:-

=A2+(B2/24)+(C2/1440)

format this as something like "ddmmmyy hh:mm"

Hope this helps

GaryB
 

nihilan

New Member
Joined
Mar 16, 2004
Messages
4
Thanks for the help. So i've got the dates and times into one column. Now I essentially have 3 columns I want to work with, Date/time, frequency, and signal strenght. I have about 11,000 entries. I want to create a chart that shows time along the x axis, signal strength along the y and a different line for each of the 20 frequencies that I have.

Any ideas how to do this?


sample data:

Frequency Signal Strength Time/date
1012 80 2/18/00 12:19
1012 80 2/18/00 12:22
1012 87 2/18/00 15:31
1012 83 2/18/00 15:44
1012 82 2/18/00 15:57
1012 92 2/18/00 16:03
1012 85 2/18/00 16:15
1012 81 2/18/00 16:22
1012 84 2/18/00 16:31
1012 76 2/19/00 6:29
1012 78 2/19/00 6:33
1012 92 2/21/00 16:53
1012 107 2/22/00 14:33
1012 105 2/22/00 14:47
1012 103 2/22/00 14:53
1012 94 2/22/00 15:02
1012 104 2/22/00 15:10
1023 84 2/13/00 20:30
1023 84 2/13/00 20:43
1023 87 2/13/00 20:59
1023 85 2/13/00 21:08
1023 84 2/13/00 21:10
1023 85 2/13/00 21:23
1023 81 2/13/00 21:30
1023 82 2/13/00 21:48
1023 81 2/13/00 21:50
1023 81 2/13/00 22:01
1023 82 2/13/00 22:17
1023 89 2/13/00 22:26
1023 84 2/13/00 22:30
1023 83 2/13/00 22:46
1023 82 2/13/00 22:51
1023 86 2/13/00 23:08
1023 84 2/13/00 23:11
1023 80 2/13/00 23:26
1023 79 2/13/00 23:31


Thanks again for any help.
 

GaryB

Active Member
Joined
Feb 25, 2002
Messages
459
I'm not too hot on graphs, but I think you're going to need to produce a pivot table from your data and produce a pivot chart from that.

HTH
GaryB
 

Watch MrExcel Video

Forum statistics

Threads
1,123,482
Messages
5,601,920
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top