Welcome to the MacNN Forums.

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

You are here: MacNN Forums > Software - Troubleshooting and Discussion > Applications > Excel, insert DAY of the week.

Excel, insert DAY of the week.
Thread Tools
MacNNUK
Grizzled Veteran
Join Date: Aug 2007
Location: U.K.
Status: Offline
Reply With Quote
Jun 10, 2011, 07:08 AM
 
Updating several Excel spreadsheets every day, I found this useful tip.

To insert day of the week into Excel, if you have the date in for example cell G1, perhaps inserted by ctrl and ;
to get the day of the week in the adjacent cell (H1) type
=TEXT(G1, "dddd")
It works wherever you place the day function. It doesn't have to be the adjacent cell.
It also works with the "Now" function, in for example G1.
(I don't use the Now function, I like to see when I last updated a SS,
nor Auto Update in a Word document for the same reason...when I wrote a letter or doc.)
However, since typing this, I use =NOW() function in addition to date, in a pair of separate cells as it auto updates with time whenever I update new info in the SS (or view) , so I have autodate, plus my changed date.

Eg: UK Date format:

10/06/2011 Friday
( Last edited by MacNNUK; Aug 11, 2011 at 03:43 PM. )

iMac Intel Core i5, 2.5GHz, 4GB RAM, 500GB 21.5" Monitor 10.8.3.
iMac 17" 2.0ghz Intel Core 2 Duo w 3gb memory (White one) 10.6.8.
Internal 500gb / 8x external HDD's 250GB - 3TB (4x Time Machine)
     
ghporter
Administrator
Join Date: Apr 2001
Location: San Antonio TX USA
Status: Offline
Reply With Quote
Jun 10, 2011, 10:06 PM
 
I always wound up using a lookup table from the "day of week" numeric value returned by the day of week function. Boy, knowing the TEXT() function would do that would have been pretty darn handy. Would that by chance work for the year with "=TEXT(G1, yyyy)"? Gotta find time to try that...

Glenn -----OTR/L, MOT, Tx
     
turtle777
Clinically Insane
Join Date: Jun 2001
Location: planning a comeback !
Status: Offline
Reply With Quote
Jun 11, 2011, 12:57 AM
 
Yes, although, your syntax is slightly off.

=TEXT(G1,"YYYY")

-t
     
MacNNUK  (op)
Grizzled Veteran
Join Date: Aug 2007
Location: U.K.
Status: Offline
Reply With Quote
Jun 11, 2011, 02:27 AM
 
Originally Posted by turtle777 View Post
Yes, although, your syntax is slightly off.

=TEXT(G1,"YYYY")

-t
and...
=TEXT(G1,"mmmm")

Any more ?

iMac Intel Core i5, 2.5GHz, 4GB RAM, 500GB 21.5" Monitor 10.8.3.
iMac 17" 2.0ghz Intel Core 2 Duo w 3gb memory (White one) 10.6.8.
Internal 500gb / 8x external HDD's 250GB - 3TB (4x Time Machine)
     
ghporter
Administrator
Join Date: Apr 2001
Location: San Antonio TX USA
Status: Offline
Reply With Quote
Jun 11, 2011, 08:40 AM
 
Oh yeah, the quotes... They get me almost every time.

Glenn -----OTR/L, MOT, Tx
     
turtle777
Clinically Insane
Join Date: Jun 2001
Location: planning a comeback !
Status: Offline
Reply With Quote
Jun 11, 2011, 10:23 AM
 
Originally Posted by MacNNUK View Post
Any more ?
Generally, the TEXT() function will return the same results as being used for custom date formats:

YYYY for full years (e.g. "2011")
YY for two digit year ("11")
MMMM for full month
MMM for month in three letters (e.g. "Dec")
DDDD for full day names
DDD for days in three letters (e.g. "Sat")

You get the idea.

When you work with dates, but you'd only want to display the Year or month and year in that particular cell, you would use custom date formats. The basically use the idea from above.

E.g., date of 4/1/2011

Custom formats (right click, format cells..., numbers, custom, enter output format):
MMM YY = Apr 11
YYYY = 2011
DDDD = Friday

-t
     
MacNNUK  (op)
Grizzled Veteran
Join Date: Aug 2007
Location: U.K.
Status: Offline
Reply With Quote
Aug 11, 2011, 01:50 PM
 
Thanks for all the above.

Excel Time calculation ?

I've been playing around with TIME & NOW (format as time) to try and get an updated time in a cell, prior to calculating how long to midnight each day, but having no success.

NOW (format cell as time) gives me a visual current time, but not one I can calculate with.

I want to calculate avg mb / hour remaining until my unlimited time starts at midnight, based on daily average of 323mb with a 10gb monthly allowance.

ie 323 minus todays' mb over hours remaining in an extra column.
At 8am it would be 323/16 ≈ 20 mb per hour
At noon it would be 323/12 ≈ 27 mb per hour if no mb downloaded, ie iMac off, etc



Help please.

I've found that =24-MOD(NOW(),1) (cell $K$3) combined with format cell as time gives me the difference in hours, but how do I divide remaining mb by these remaining hours ?
( Last edited by MacNNUK; Aug 11, 2011 at 09:24 PM. )

iMac Intel Core i5, 2.5GHz, 4GB RAM, 500GB 21.5" Monitor 10.8.3.
iMac 17" 2.0ghz Intel Core 2 Duo w 3gb memory (White one) 10.6.8.
Internal 500gb / 8x external HDD's 250GB - 3TB (4x Time Machine)
     
turtle777
Clinically Insane
Join Date: Jun 2001
Location: planning a comeback !
Status: Offline
Reply With Quote
Aug 11, 2011, 03:50 PM
 
Originally Posted by MacNNUK View Post
Thanks for all the above.

I've been playing around with TIME & NOW (format as time) to try and get an updated time in a cell, prior to calculating how long to midnight each day, but having no success.

NOW (format cell as time) gives me a visual current time, but not one I can calculate with.
Huh, =NOW() can be calculated with. Remember, a date is nothing but a number with many decimals.

Time remaining in the day: =ROUNDUP(NOW(),0)-NOW() ; format as h:mm:ss

Originally Posted by MacNNUK View Post
I want to calculate avg mb / hour remaining till my unlimited time starts at midnight, based on daily average of 323mb with a 10gb monthly allowance.

ie 323 minus todays' mb over hours remaining in an extra column.
At 8am it would be 323/16 ≈ 20 mb per hour
At noon it would be 323/12 ≈ 27 mb per hour if no mb downloaded, ie iMac off, etc
To extract hours or minutes from the above, use
=HOUR(ROUNDUP(NOW(),0)-NOW())
or
=MINUTE(ROUNDUP(NOW(),0)-NOW())

To get the remaining hours with fractional minutes, use this:
=HOUR(ROUNDUP(NOW(),0)-NOW())+MINUTE(ROUNDUP(NOW(),0)-NOW())/60

Your complete calculation would be this:
=323/HOUR(ROUNDUP(NOW(),0)-NOW())+MINUTE(ROUNDUP(NOW(),0)-NOW())/60

There might be smarter ways than this, but at least it works

-t
     
MacNNUK  (op)
Grizzled Veteran
Join Date: Aug 2007
Location: U.K.
Status: Offline
Reply With Quote
Aug 11, 2011, 04:00 PM
 
Originally Posted by turtle777 View Post
To get the remaining hours with fractional minutes, use this:
=HOUR(ROUNDUP(NOW(),0)-NOW())+MINUTE(ROUNDUP(NOW(),0)-NOW())/60

Your complete calculation would be this:
=323/HOUR(ROUNDUP(NOW(),0)-NOW())+MINUTE(ROUNDUP(NOW(),0)-NOW())/60

There might be smarter ways than this, but at least it works

-t
Thanks turtle777, I can see you have put a lot of thought into this.

Your complete calculation would be this:
=323/HOUR(ROUNDUP(NOW(),0)-NOW())+MINUTE(ROUNDUP(NOW(),0)-NOW())/60


I need to use the remaining mb of 194 in the cell, not the average of 323.

But when I substitute cell G27 for 323, your formula doesn't work for me.

However...

When I use your previous formula
To get the remaining hours with fractional minutes, use this:
=HOUR(ROUNDUP(NOW(),0)-NOW())+MINUTE(ROUNDUP(NOW(),0)-NOW())/60

and divide G27 (mb remaining) by the decimal hours your formula gives me, I get the correct answer !!
Cell I27.....(=G27/$L$4) gives me -70 (per hour) , the correct result.





Once again, thanks.
( Last edited by MacNNUK; Aug 11, 2011 at 09:24 PM. )

iMac Intel Core i5, 2.5GHz, 4GB RAM, 500GB 21.5" Monitor 10.8.3.
iMac 17" 2.0ghz Intel Core 2 Duo w 3gb memory (White one) 10.6.8.
Internal 500gb / 8x external HDD's 250GB - 3TB (4x Time Machine)
     
turtle777
Clinically Insane
Join Date: Jun 2001
Location: planning a comeback !
Status: Offline
Reply With Quote
Aug 11, 2011, 04:33 PM
 
I dunno what's going on. You should be able to substitute the 323 with any cell reference.

What Excel are you using ?

-t
     
MacNNUK  (op)
Grizzled Veteran
Join Date: Aug 2007
Location: U.K.
Status: Offline
Reply With Quote
Aug 11, 2011, 04:45 PM
 
Originally Posted by turtle777 View Post
I dunno what's going on. You should be able to substitute the 323 with any cell reference.

What Excel are you using ?

-t
Excel 12.3.0, updated last week.

=(G27)/HOUR(ROUNDUP(NOW(),0)-NOW())+MINUTE(ROUNDUP(NOW(),0)-NOW())/60
gives me -70.09, not -63.90 the correct figure (at this time)

Again, thank you.
( Last edited by MacNNUK; Aug 11, 2011 at 09:25 PM. )

iMac Intel Core i5, 2.5GHz, 4GB RAM, 500GB 21.5" Monitor 10.8.3.
iMac 17" 2.0ghz Intel Core 2 Duo w 3gb memory (White one) 10.6.8.
Internal 500gb / 8x external HDD's 250GB - 3TB (4x Time Machine)
     
turtle777
Clinically Insane
Join Date: Jun 2001
Location: planning a comeback !
Status: Offline
Reply With Quote
Aug 11, 2011, 04:52 PM
 
Originally Posted by MacNNUK View Post
=G27/(HOUR(ROUNDUP(NOW(),0)-NOW())+MINUTE(ROUNDUP(NOW(),0)-NOW())/60)
Fixinated.

-t
     
MacNNUK  (op)
Grizzled Veteran
Join Date: Aug 2007
Location: U.K.
Status: Offline
Reply With Quote
Aug 11, 2011, 04:57 PM
 
Originally Posted by MacNNUK View Post
=G27/(HOUR(ROUNDUP(NOW(),0)-NOW())+MINUTE(ROUNDUP(NOW(),0)-NOW())/60)


Originally Posted by turtle777 View Post
Fixinated.

-t
Those brackets get you every time !

Brilliant, that's the one !!

Thank you.

iMac Intel Core i5, 2.5GHz, 4GB RAM, 500GB 21.5" Monitor 10.8.3.
iMac 17" 2.0ghz Intel Core 2 Duo w 3gb memory (White one) 10.6.8.
Internal 500gb / 8x external HDD's 250GB - 3TB (4x Time Machine)
     
   
Thread Tools
 
Forum Links
Forum Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Top
Privacy Policy
All times are GMT -4. The time now is 02:48 AM.
All contents of these forums © 1995-2017 MacNN. All rights reserved.
Branding + Design: www.gesamtbild.com
vBulletin v.3.8.8 © 2000-2017, Jelsoft Enterprises Ltd.,