CODEWITHSUNDEEP
exceltime
Waqas Tariq
Waqas Tariq

Reputation: 105

Convert over 24Hr time into 24Hr time

My data contains a row (A2:A40000) that contains accumulated hourly time. I am trying to convert it to 24 hour time, example: 24 to 0, 57 to 9 and so on.

Once that is done, I also want to get which day the time falls on, based on column A, assuming the first 0 starts on Monday 12:00AM, that would mean that Hr 24 will be Tuesday Hr 57 will be Wednesday and so on.

The end goal is to create a whisker chart with Day and time on X and column B on Y.

I would appreciate any help to get me started.

Example:

 A       B
0.00    0.00
1.00    0.00
1.00    0.00
2.00    0.00
12.00   2.00
14.00   0.00
16.00   0.00
17.00   0.00
17.00   0.00
18.00   0.00
19.00   10.00
22.00   0.00
23.00   0.00
24.00   1.00
26.00   0.00
28.00   0.00
46.00   0.00
58.00   0.00
10240.00    0.00

Upvotes: 0

Views: 52

Answers (2)

user8261831
user8261831

Reputation: 504

In your case to get hour of day, do =HOUR(1+A1/24), you have to do +1 since you start with zero.

Hope this helps

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

to get the hour of the day:

=HOUR(1+A1/24)

To get the weekday(Numerical)

=WEEKDAY(2+A1/24)

To get the Weekday(Name)

=CHOOSE(WEEKDAY(2+A1/24),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

enter image description here

Upvotes: 1

Related Questions