The Naked Scientists

The Naked Scientists Forum

Author Topic: excell or access  (Read 1768 times)

Offline sakamskopsko

  • First timers
  • *
  • Posts: 2
    • View Profile
excell or access
« on: 29/06/2012 00:45:37 »
Iím new to this forum.
I need to display a three digit number and date to automatically updates/advances itself every time on midnight.
Can somebody help me in solving this, its for my small business.
Tried to use excel but with no luck, I can have it update by only refreshing it manually, I would like it to be done automatically and not relay on somebody to do it or not.
Thanks


 

Offline CliffordK

  • Neilep Level Member
  • ******
  • Posts: 6321
  • Thanked: 3 times
  • Site Moderator
    • View Profile
Re: excell or access
« Reply #1 on: 29/06/2012 01:20:02 »
In Excel, have you tried the function:

=now()
or
=TODAY()


I think in Access you use:

Date()
or
now()
 

Offline CliffordK

  • Neilep Level Member
  • ******
  • Posts: 6321
  • Thanked: 3 times
  • Site Moderator
    • View Profile
Re: excell or access
« Reply #2 on: 29/06/2012 08:35:25 »
Oh, and for the 3 digit number.

There are a few solutions.

One might be to store the last date and current date.  If they are different, then to increment the number by one.  However, that would be dependent on the spreadsheet being opened on consecutive dates.  Often you can subtract dates like numbers, and might be able to get around that problem.

Another solution would be to use the Julian Date formula.  You can either write your own formula, or you can use the built in Excel functions.

Excel considers a date as a Julian date, assuming you get the cell formatted as you wish, you can make a date return a number.  The function Date(Y,M,D) will return the Julian Date that Excel uses for a particular day.

So, for example,

=trunc(now())-date(2012,1,1)
or
=today()-date(2012,1,1)

will return the current day of the year, with January 1 being zero (0).   June 29, 2012 would be 180.  Add 1 if you want January 1 to be 1.

Using the Mod function.  If Mod(year,4) is equal to zero, then it is a leap year for all years from 1901 to 2099.
« Last Edit: 29/06/2012 08:38:04 by CliffordK »
 

Offline sakamskopsko

  • First timers
  • *
  • Posts: 2
    • View Profile
Re: excell or access
« Reply #3 on: 30/06/2012 02:40:58 »
Yes, i need to change the julian date and used by date to update automaticly.
But for what ever reasons the formula i tried it showed error and in the field "#NAME".
Will it update automaticly at midnight if i leave the excel on?
Thanks for your fast response and help.
 

The Naked Scientists Forum

Re: excell or access
« Reply #3 on: 30/06/2012 02:40:58 »

 

SMF 2.0.10 | SMF © 2015, Simple Machines
SMFAds for Free Forums