[MacNNUK]

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

[ghporter]

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...

[turtle777]

Yes, although, your syntax is slightly off.

=TEXT(G1,"YYYY")

-t

[MacNNUK]

Originally Posted by turtle777 

Yes, although, your syntax is slightly off.

=TEXT(G1,"YYYY")

-t

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

Any more ?

[ghporter]

Oh yeah, the quotes... They get me almost every time.

[turtle777]

Originally Posted by MacNNUK 

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]

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 ?

[turtle777]

Originally Posted by MacNNUK 

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 

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]

Originally Posted by turtle777 

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.

[turtle777]

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]

Originally Posted by turtle777 

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.

[turtle777]

Originally Posted by MacNNUK 

=G27/(HOUR(ROUNDUP(NOW(),0)-NOW())+MINUTE(ROUNDUP(NOW(),0)-NOW())/60)

Fixinated.

-t

[MacNNUK]

Originally Posted by MacNNUK View Post
=G27/(HOUR(ROUNDUP(NOW(),0)-NOW())+MINUTE(ROUNDUP(NOW(),0)-NOW())/60)

Originally Posted by turtle777 

Fixinated.

-t

Those brackets get you every time !

Brilliant, that's the one !!

Thank you.