# Excel and Date Functions



## Dryver14 (Dec 1, 2011)

Am I the only person that thinks Excel is sadly lacking in date functions?

Is it too hard for them to do?

So many questions generated here are the same and I think excel must have spotted this calculating time and changing time from GMT to UTC etc are very common actions for excel users.


----------



## Michael M (Dec 1, 2011)

What !!!!....you actually want Bill (Gates) to do something extra for the masses, Shame on you, after all he's done for you.

But honestly, I think date stuff is too hard, that's why blokes like Chip Pearson have to have websites to do it for them !!
Oh, the heaven of it........A date function Addin !!!


----------



## Oaktree (Dec 1, 2011)

I'd be content if they just added DATEDIF to Excel help's documentation and stopped having it be a stealth function.


----------



## diddi (Dec 1, 2011)

so how about a bit of a group effort to

a) construct a list of useful date and time functions and then
b) design and implement a free addin for fellow mrexcelers

i have coded for astronomical date and time functions if the masses think that they would be useful (eg julian date<>calendar date)


----------



## Domski (Dec 2, 2011)

Oaktree said:


> I'd be content if they just added DATEDIF to Excel help's documentation and stopped having it be a stealth function.



And fix the bugs in it so you're sure it giving the correct results.

Dom


----------



## jasonb75 (Dec 2, 2011)

Dryver14 said:


> changing time from GMT to UTC


 
I'm pretty sure =A1/1^9.9E+307 works for that, as does changing / to * to convert the other way.


----------



## xenou (Dec 2, 2011)

Changing time from GMT to UTC?


----------



## xenou (Dec 2, 2011)

> Quote:
> Originally Posted by Dryver14 View Post
> changing time from GMT to UTC
> I'm pretty sure =A1/1^9.9E+307 works for that, as does changing / to * to convert the other way.



Brilliant.  Works for me:
<img alt="screenshot" src="http://northernocean.net/etc/mrexcel/20111202_utc.png" />


----------



## jasonb75 (Dec 2, 2011)

xenou said:


> Brilliant. Works for me:


 
There was me thinking it would go un-noticed


----------



## xenou (Dec 2, 2011)

Methinks you have a future as a MS software engineer if you keep this up


----------



## Dryver14 (Dec 3, 2011)

No matter how stupid the person asking the question as a visit a day reminds me on here the point is relevant. GMT:EST or what ever silly times Americans have. Time starts here in Gods country, no wonder I put the same time zone twice why would anyone want to be anywhere else.

High fuel prices High taxes high national insurance bad weather .....we have it all.


----------



## xenou (Dec 3, 2011)

^^  We're hoping to catch up to you!  In regards to dates/times, I find date formatting as the main "common" problem.  I rarely see "conversion to a different time zone" question, so I may not share a sense of frustration on that particular point.


----------



## T. Valko (Dec 3, 2011)

xenou said:


> Methinks you have a future as a MS software engineer if you keep this up


 

It would have been nice to have a "count weekdays between" function in Excel versions prior to Excel 2010. For example, count how many Tuesdays are between a start date and an end date and exclude these dates form the calculation.

I think that can be done in Excel 2010 with the new NETWORKDAYS.INTL function.

Honestly, I don't understand why there aren't many, many, many, many more functions available. I would think that developing worksheet functions is a fairly trivial task as far as developing the Excel application as a whole is concerned. Yet, version after version we see very few new functions added.

I wish I was in charge! Excel would be so much different than it is.

For example, there would be a single worksheet function library that can be downloaded in which ALL functions would work in EVERY version of Excel. They probably think that's not a good idea because that might keep people from upgrading to newer versions. I don't know if that's a realistic concern or not. I've never thought of upgrading just so I could use new functions (which are few and far between in new releases).


----------



## xenou (Dec 3, 2011)

In defense of Excel I don't see other spreadsheet programs employing vastly more or different date/time functions (such as OOorg/LibreOffice or Gnumeric).  Though I see Apple's _Numbers_ has "duration" functions which is an interesting addition.  We may see that in Excel too I should think (coming over from XML).  But be careful what you ask for.  We *could* end up with hundreds of functions (e.g., information overload) which has its own "cost" of confusing people, making it hard to learn, inhibiting compatibility, and complicating data-sharing and portability.  I don't think it is as easy as it sounds making an advanced product that meets the needs of hundreds of different kinds of users and purposes, from mom and dad keeping a wedding guest list to astrophysicists crunching data and everything in between.


----------



## Dryver14 (Dec 3, 2011)

I understand the point about calculation overload and to be fair it was only a question regarding GMT to EST that triggered my initiation of the question, but certain repeat questions around work hours and spannning the midnight hour etc to calculate work time would not be unreasonable as companies pay more for the programme.

As for protecting against dumming down .......it's too late for me...save yourself.


----------

