Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.A lot of people get confused when working with dates. But they're really rather simple when you get to know them.
All dates and times are stored as numbers, the unit being days. Specifically, they're stored as the number of days since 1 January 1900. (There's a slight quirk that means that Excel counts one day too many, but that's not important right now.)
Today, 1 June 2011, is stored as 40,695. That's 40,695 days since the start of the last century. If you simply input a date, it will store it as the beginning of that day, or 00:00:00. If you want to reference midday on that day, you simply add 0.5 (half a day). And you can add any unit of time simply by adding it (in days) to the number of the date you're referring to.
So 2019 (19 minutes past eight in the evening) today is stored as 40695.846528 (or 40695 + 1219/1440 (1219 minutes gone out of 1440 in the day).
Even when you see a time displayed without a date, it always has a date associated with it. It's only its display settings that prevents it from being shown.
So if you want to add 12 hours to a timestamp, add 0.5. If you want to add three days and three minutes, add 3+3/1440. Excel will do the calculations and your display settings will show your time and dates accurate to the second. Job done.
A word of warning: take care when working across geographies. American dates are displayed as MM/DD/YYYY, whereas British ones are displayed as DD/MM/YYYY. Whenever there's a risk of confusion, display your dates with the month written out as a word or an abbvreviated word. This will ensure that people are happy when you produce that deliverable by 11 December, and that no one expected it on 12 November.
Hi,
Can you help me with a formula. I want to create a formula that works out how many weeks a person has had to wait for an appointment. For example:column h2 has the date a person was entered onto the spreadsheet, i.e. 10/03/15 and column i2 has the date a person was seen, i.e. 11/21/15. I want column j2 to automatically tell me how many weeks that person has had to wait.
Thanks
=ROUNDUP(I2-J2)/7,0)
This will treat anything less than a week as one week. Seven days would be 1; eight days would be 2.
Or:
=ROUNDDOWN(I2-J2)/7,0)
This will treat anything less than a week as 0. Six days would be 0; seven days would be 1.
Hope this helps.
Dan.
hi, i gotta excel spreadsheet with employee commencement date and leave entitlement. after completion of every year, i want to add 1 day to the existing entitlement. can u pls help me with a formula?
Thanks
Abilash
If join date is in A2 and core entitlement is in B2, then
=B2 + rounddown((today()-A1)/365.25,0)
Hi Dan,
Thanks heaps.... It works.... 🙂
(in your reply, it should be A2 instead of A1)
Abilash
Hi Dan,
in the same formula, is it possible to cap the maximum number of leave to 14
Thanks
Abilash
=MIN(14, B2 + rounddown((today()-A2)/365.25,0))
Thanks Dan...
I have an spreadsheet with employee leave details...
How can i track the leave of an employee, as on date
Eg: Annual Leave entitlement - 14, Date of join - 05 May 2015, Leave entitlement as on date - ?????
If annual leave entitlement is in cell A5, and join date is in B5, then
=rounddown((today()-B5)/365.25*A5,0)
Halhelujal! I needed this-you're my savior.
Hi there I'm trying to build a link between a set of schedules and the responses they got during those shifts. to populate a table of each persons responses is this possible? date and time of response are in two separate sells