Concerting Time to Degrees

18:00:00 CANNOT be both 0 and 360 ...

What about 17:00 and 19:00???

Please try in B2 and copy down:

Formula:

Please Login or Register  to view this content.

Is there a degrees format in excel?

Set a custom format:

0"°"

Or change the formula:

=MOD((A2-TIME(18,0,0)),1)*360&"°"

EDIT: ° is ALT+0176 on the numeric keypad.

Are we any closer to a solution now???

Also custom formatting without "" does the job: (only 0°)

Note: ° = Press ALT and type 0176

Yes, you're right, Hans.

It works in the excel spreadsheet you gave me

when I try to hold the the "alt" key and type in "0176" or "+0176" I can't do it on my own

I can copy and paste between spreadsheets and it works

wonder why it isn't working on my own

You need to make sure that the numeric keypad (NumLock) is set to ON.

ROUND works fine:

=ROUND(MOD((A2-TIME(18,0,0)),1)*360,0)&"°"

BEAUTIFULLLLLLLLLLLLLLLLL

I had the ",1" in the wrong place

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Thanks...I hope everyone got my add reputation I did

I can't do it on my own

May be you are using a MAC? ALT0176 works only on Windows.

Thanks for the feedback and rep . Glad to have helped.

If the OP is on a Mac, then their profile should show this (e.g. 2019 Mac).

Thanks for the feedback and reputation, glad to have helped. .

If you want 72 degrees per day, startting with date A2 and time C2:

Please try this and copy down:

Formula:

Please Login or Register  to view this content.

But if you start Monday 6:00 PM with 0°, then you will reach 360° on Saturday, because Saturday is 360/72 = 5 days after Monday.

If you want reach 360° on Friday, you should replace in the formula 72 with 90 (90 = 360/4), because Friday is 4 days after Monday.

Originally Posted by HansDouwe

Thanks for the feedback and reputation, glad to have helped. .

If you want 72 degrees per day, startting with date A2 and time C2:

Please try this and copy down:

Formula:

Please Login or Register  to view this content.

But if you start Monday 6:00 PM with 0°, then you will reach 360° on Saturday, because Saturday is 360/72 = 5 days after Monday.

If you want reach 360° on Friday, you should replace in the formula 72 with 90 (90 = 360/4), because Friday is 4 days after Monday.

No problem thanks for the help.

Ok so we need to explain a little bit more here what's going on this kind of works but it only worked for the first week then the degrees keep going higher instead of resetting after a week.

So I am trying to do this for the day, week, month, year

column G "IDEG" works perfect the day's start at 6:00 pm est and that is 0 degrees and they end at 5:00 pm est which is 345 degs and they start again at 0 degrees so this works perfect
column H "WDEG" is a week which is 360 degrees and it starts sunday at 6:00 pm and ends Friday at 5:00 pm which is 345 degrees, then resets to 0 degrees every sunday
column I "MDEG" is a month which is 360 degrees and it starts on 6:00 pm on the first of the month and ends at 6:00 pm the last day of the month, it should reset to 0 degrees every month
column J "YDEG" is a year which is 360 degrees and it starts on 6:00 pm on the first day of the year and ends at 6:00 pm the last day of the year, so the sheet should go from 0 to 360 the entire year and then reset the next year

I have attached a spreadsheet with more than a months data in a new column IDATA (2)

I hope I am starting the problem clearly? I can further elaborate if needed.

The day ends at 5:59:59.99... PM which is 359.99.. degrees.
I say for convenience. The day ends at 6 PM which is 360 degrees.

The week ends Friday at 5:00 pm which is 345 degrees

. Why does the week not ends at Friday 6 PM which is 360 degrees?
In that case Friday 5:00 PM is 357 degrees (not 345 degrees), because than we should counting 3 degrees pro hour,

the week starts at Sunday 6:00 pm

So Monday 6:00 PM is 72 degrees. Is that OK?

The month starts on 6:00 pm on the first of the month and ends at 6:00 pm the last day of the month

What should the month formula return between the last day of the month 6:00 PM and the first day of the next month 6:00 PM?
For example The first day of the month at 9:00 AM.

Not every month is counting in the degrees in the same speed, because not every month has the same length.
Is that OK?

Not every year is counting the degrees in the same speed, because not every year has the same length.
[A leap year is 366 days and other years are 365 days]
Is that OK.

What should the year formula return between the last day of the year 6:00 PM and the first day of the next year 6:00 PM?

What should the week formula return between Friday 6 PM and Sunday 6 PM?

Thanks for the answers:

The month formula ends also at the first day of the month 6:00 PM. That is 360 degrees.
OK I understand. So if the month has 30 days the formula should counting 360/30 is 12 degrees each day.
That is easy.

But I don't understand the annual calculation. In the first place, a leap year can also include an extra working day. That depends on what the first day of the leap year is.
2024 has 53 Mondays, 53 Tuesdays, 52 Wednesdays, 52 Thursdays and 52 Fridays. Total is 262.

In addition, I don't understand your year calculation over the working days. Could you give an example of what the annual formula should indicate this year on Monday, January 1st 6 PM, Friday, January 5th 6 PM, Sunday January 7th 6 PM, Friday January 12th 6 PM and Sunday January 14th 6 PM?

And what should the week formula return between Friday 6 PM and Sunday 6 PM?

Originally Posted by HansDouwe

Thanks for the answers:

The month formula ends also at the first day of the month 6:00 PM. That is 360 degrees.
OK I understand. So if the month has 30 days the formula should counting 360/30 is 12 degrees each day.
That is easy.

But I don't understand the annual calculation. In the first place, a leap year can also include an extra working day. That depends on what the first day of the leap year is.

In addition, I don't understand your year calculation over the working days. Could you give an example of what the annual formula should indicate this year on Monday, January 1st 6 PM, Friday, January 5th 6 PM, Sunday January 7th 6 PM, Friday January 12th 6 PM and Sunday January 14th 6 PM?
Yeah you are right the leap year can start and end in the middle of the week so we would have extra working days.
Monday, January 1st 6 PM - 0 degrees
Friday, January 5th 6 PM - 4.918033 degrees
Sunday January 7th 6 PM - 4.918033 degrees
Friday January 12th 6 PM - 9.836066 degrees
Sunday January 14th 6 PM - 9.836066 degrees

These are like the 0/360 for the days when it hits 6PM it is 0 and 360
Same for the weeks

And what should the week formula return between Friday 6 PM and Sunday 6 PM?

The formula should ignore the time between Friday 6PM and Sunday 6PM.
It should return nothing since that's how the data is structured in the excel spreadsheet.

OK, you don't have times between friday 6 PM and Sunday 6 PM in your sheet. Right?

Could you also answer my questions about the annual formula?

I don't understand why you count the same degrees for the first week as for the second week.
The first week only has 4 days (no Sunday on Monday), the second week has 5 days.

In addition, you now count 360/365 degrees per working day. This means you only end up at 256 degrees after 52 weeks.

Now that you want to stop the degrees clock for 48 hours every week for the annual formula, it is better to make the formula such that we end up at 360 degrees exactly on the last working day of the year at 6 PM. Regardless of whether it is a leap year or not. A year can contain 260, 261 or 262 working days, but if I have to count from January 1, 6:00 PM to December 31, 6:00 PM, that is 364 days in a regular year or 365 days in a leap year. And those years can contain 260 or 261 working days (depending on the day the year begins.) Is that OK?

Should the monthly clock run during the weekends or should it also run faster during the week, so that it can be stopped during the weekend?

I was still thinking about the monthly clock and the yearly clock.
The weekly clock lets you start at 6 PM for the first working day of the week and end up at 6 PM on Friday exactly 5 days later.

Then it also seems logical to me to have the monthly clock start at 6 PM of the last day of the previous month (and to run until 6 PM of the last (working) day of the month.
The same for the annual clock.

How do you think you about that?

Custom format cells 0° or 0.00°

IDEG: Day = 18:00:00 - 18:00:00 Please try:

Formula:

Please Login or Register  to view this content.

WDEG: Week = SUN 18:00:00 - FRI 18:00:00:

Formula:

Please Login or Register  to view this content.

MDEG: Month = last day previous month 18:00:00 - last day month 18:00:00 (and does not count FRI 18:00:00 - SUN 18:00:00):

Formula:

Please Login or Register  to view this content.

YDEG: Year = last day previous year 18:00:00 - last day year 18:00:00 (and does not count FRI 18:00:00 - SUN 18:00:00):

Formula:

Please Login or Register  to view this content.

WOW this is a great attempt! I am trying to get the logic of the formulas.

Unfortunately the WDEG isn't working it goes until 522 degrees then resets, the deg count should reset at sunday at 6:00 pm = 0 deg

The MDEG and YDEG are giving me errors my excel 2019 doesn't like the formula #NAME?

I will play around a little bit see if I an fix it ............WOW you did great with these formulas

The MDEG and YDEG are giving me errors my excel 2019 doesn't like the formula #NAME?

LET is only available in 2021 and 365.

Try:

=(NETWORKDAYS(1+EOMONTH(D2+1/4,-1),D2+1/4)-1+MOD(D2+1/4,1))/NETWORKDAYS(1+EOMONTH(D2+1/4,-1),EOMONTH(D2+1/4,0))*360

and:

=(NETWORKDAYS(1+EOMONTH(D2+1/4,-1),D2+1/4)-1+MOD(D2+1/4,1))/NETWORKDAYS(1+EOMONTH(D2+1/4,-MONTH(D2+1/4)),EOMONTH(D2+1/4,12-MONTH(D2+1/4)))*360

See post #34!!!

WOW this formula works...........wow great job!

Our math is wrong somewhere.........we get errors in the degrees see attached spreadsheet.......I have to think about this a while .......we may want the month and years to begin at midnight and the day and week to start at sunday at 6:00 pm......I think this will clear up the errors.........
This is it we need the month and year degs to start at midnight.......
the day and week degrees have to start sunday at 6:00 pm and end friday at 6:00 pm.
So the day degrees work with the current formula
the week degrees don't work it doesn't reset properly
the monthly and yearly degrees have to start at midnight and end at midnight........


WE ARE SOOOOO CLOSEEEEEEE

please see attached spreadsheet

I can't help with the maths - sorry.

Thanks for the feed back and rep . Glad to have helped.

Now de finishing touche,

the monthly and yearly degrees have to start at midnight and end at midnight........

OK. Does this rule still apply?

The monthly clock should stop during the weekends, we only want the monthly clock to run during working days, from 6:00 pm Sunday to 6:00 pm Friday

The error in the weekly clock on Sunday evening is clear to me and I will correct it.

Yes the monthlyl close should stop on weekends..........so it should run from Midnight on the 1st until Friday 6:00 pm, then pause and start up again on Sunday 6:00 pm, and run until midnight on the last day of the month
same with the yearly, it should start on Midnight on the 1st, and run until Friday 6 pm, then pause until sunday 6:00 pm before resuming.
Thsi was my mistake on the math at the beginning......AliGWs equations work over here

OK. The requirements are clear to me. I'll fix that Wednesday night.

I am exciteddddddddddddddddddddddddddd

Here are the new formulas belonging to these conditions:

IDEG: Day = 18:00:00 - 18:00:00

WDEG: Week = SUN 18:00:00 - FRI 18:00:00

MDEG: Month = first day month 00:00:00 - last day month 24:00:00
(and does not count FRI 18:00:00 - SUN 18:00:00

YDEG: Year = first day year 00:00:00 - last day year 24:00:00
(and does not count FRI 18:00:00 - SUN 18:00:00)

IDEG: No change
WDEG: Please try:

Formula:

Please Login or Register  to view this content.

MDEG: Please try:

Formula:

Please Login or Register  to view this content.

`
YDEG: Please try:

Formula:

Please Login or Register  to view this content.

The weekly function is working!!!! perfectly as well

however my version of excel doesn't let me use the "LET" function
here are the formulas that ALIGW gave but that do not reset properky
MDEG
=(NETWORKDAYS(1+EOMONTH(D2+1/4,-1),D2+1/4)-1+MOD(D2+1/4,1))/NETWORKDAYS(1+EOMONTH(D2+1/4,-1),EOMONTH(D2+1/4,0))*360
YDEG
=(NETWORKDAYS(1+EOMONTH(D2+1/4,-1),D2+1/4)-1+MOD(D2+1/4,1))/NETWORKDAYS(1+EOMONTH(D2+1/4,-MONTH(D2+1/4)),EOMONTH(D2+1/4,12-MONTH(D2+1/4)))*360

I will try to take a look to see if I can modify the formulas to work in excel 2019.......

But yesssssss the WDEGs work! Halfway done!

Thank you!!!

2019 does not have LET, as I noted in post #34.

Administrative Note re. Forum Guideline #2:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter and rarely necessary.

If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @AliGW).

If you do need to quote, limit the quoted section just to the section to which you wish to draw your helper's attention or a direct question to which you wish to respond.

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

It doesn't need a quote as it was referring to YOUR post immediately before it (post #45), where you had unnecessarily quoted the whole of post #44. I have removed some of the unnecessary quoting in this thread.

Once again, if you are answering the post immediately BEFORE your own, there is NO NEED AT ALL to quote it.

These formulas without LET works in Excel 2019:

MDEG Please try:

Please Login or Register  to view this content.

YDEG Please try:

Please Login or Register  to view this content.

The first of the month MDEG also starts at 0 degrees.
The MDEG rule is:

first day month 00:00:00 - last day month 24:00:00
(and does not count FRI 18:00:00 - SUN 18:00:00).

January 1, 2024 is a Monday and the MDEG of Monday January 1, 2024 0:00 returns 0 degrees.

Your table does not start Monday January 1, 2024 0:00, but your table start Monday January 1, 2024 18:00

Yeah you are right 18 hours is 12 degrees...............

Can we add in a quarterly clock? Where we divide the year up in quarters so every 3 months?

3 months = 360 degrees. There would be 4 of them every year.

I attached the spreadsheet and added the column in

QDEG:

Please try:

Please Login or Register  to view this content.