Today, let’s travel in time. Pack your photon ray guns, extra underwear, buckle your seat belts and open Excel!
Of course, we are not going to travel in time. (Come to think of it, we are going to travel in time. By the time you finish reading this, you would have traveled a few minutes)
We are going to learn how to travel in time when using Excel. In simple terms, you are going to learn how to move forward or backward in time using Excel formulas.
So are you ready to hit the warp speed? Let’s beam up our Excel time machine.
Tip 0 – Date & Time are an illusion
Most important tip for Excel time travelers is to understand that Excel dates & times are just numbers. So when you see a date like 17-October-2013 in a cell, you can safely assume that it is a number disguised to look like 17th of October, 2013. To see the number behind this, just select the cell and format it as number (from Home ribbon).
Now that you understood this concept, let’s jump in to the 42 tips. All these tips assume a date or time value is in the cell A1.
Staying at present:
- To have latest star date in a cell, just press CTRL+; (of course, in Excel world, star date is nothing but whatever date your computer shows)
- To have current time in a cell, just press CTRL+:
- Of course, we time travelers are lazy. So pressing CTRL+; every day or CTRL+: every second is not cool. That is why you can use =TODAY() in a cell to get today’s date. It will automatically change when you re-open the file tomorrow.
- Likewise, use =NOW() to get current date & time in a cell. Remember, although time changes every second, you will not see the cell updated unless the formula is somehow re-calculated. This is done by,
- Pressing F9
- Saving / re-opening the file
- Making any changes to any cell (like typing a value, changing a value)
- Editing the formula cell and pressing Enter
- To check if today is after or before the date in cell A1, you can use =TODAY() > A1. This will be TRUE if A1 has a past date and FALSE if A1 has a future date.
- To know how many days are there between TODAY and the date in A1, use =TODAY() – A1. This will be a negative number if A1 is a future date. To see just the number of days (without negative sign), you can use =ABS(TODAY()-A1)
- To know how many hours are left between the time in A1 and current time, use =(NOW()-A1)*24.
- While the above formula works, it shows hours and fraction. To just see hours and minutes left, you can use =TEXT((NOW()-A1), “[hh]:mm”). Note: This formula works only when A1 < NOW().
- To know how many weeks are left between TODAY() date and a future date in A1, use =(TODAY() –
A1)/7 - To know how many months are left between TODAY() and date in A1, use = DATEDIF(TODAY(), A1, “m”).
Related: How to use DATEDIF function. - To know which month is running, use =MONTH(TODAY())
- To see the month name instead of number, use =TEXT(TODAY(), “MMMM”). This shows the month’s name in your Excel language.
- To know which year is running, use =YEAR(TODAY())
- To see the last 2 digits of the year, you can use =RIGHT(YEAR(TODAY()), 2)
- To find the day of week for TODAY, use =WEEKDAY(TODAY()). This will give a number (1 to 7, 1 for Sunday, 7 for Saturday).
- To see the weekday name instead of number, use =TEXT(TODAY(), “DDDD”).
- To see today’s date alone, use =DAY(TODAY())
- To know if the present year is a leap year or not, see this.
Going back in time
- To go back by 6 days from the date in A1, use =A1-6
- To go back to last Friday use =A1-WEEKDAY(A1, 16). This works in Excel 2010, 2013. If your time machine is old (ie you have Excel 2003 or earlier versions), you can use =A1-CHOOSE(WEEKDAY(A1), 2,3,4,5,6,7,1)
- To go back by 5 weeks, use =A1-5*7
- To go back to start of the month, use =DATE(YEAR(A1), MONTH(A1),1)
- To go back to end of previous month, use = DATE(YEAR(A1), MONTH(A1),1) – 1
- Or use =EOMONTH(A1,-1)
- To go back by 2 months, use =EDATE(A1, -2)
- To go back by 27 working days, use =WORKDAY(A1, -27). This assumes, Monday to Friday as working days.
- To go back by 27 working days, assuming you follow Monday to Friday work week and a set of extra holidays, use =WORKDAY(A1, -27, LIST_OF_HOLIDAYS)
- To go back by 7 quarters, use =EDATE(A1, -7 * 3)
- To go back to the start of the year, =DATE(YEAR(A1), 1,1)
- To go back to same date last year, = DATE(YEAR(A1)-1, MONTH(A1), DAY(A1))
- To go back a decade, =DATE(YEAR(A1)-10, MONTH(A1), DAY(A1))
Going forward in time
We, time travelers are smart people. Once you know that turning the knob backwards takes you to past, you know how to go to future. So I am giving very few examples for going forward in time.
- To go to the 17th working day from date A1, assuming you use Sunday to Thursday workweek, use =WORKDAY.INTL(A1,17,7). This formula works in Excel 2010 or above.
- To go to next hour, use=A1+1/24
- To go to next day morning 9AM, use =INT(A1+1) + 9/24
- To go to 18th of next month, use =DATE(YEAR(A1), MONTH(A1)+1, 18)
- To go to end of the current quarter for date in A1, use =DATE(YEAR(A1), CHOOSE(MONTH(A1), 4,4,4,7,7,7,10,10,10,13,13,13),1)-1
- To go to a future date that is 4 years, 6 months, 7 days away from A1, use =DATE(YEAR(A1)+4, MONTH(A1)+6, DAY(A1)+7)
Finding the amount of time traveled
- To know how many days are between 2 dates (in A1 & A2), use =A1-A2
- To know how many working days are between 2 dates, use =NETWORKDAYS(A1, A2) (remember: A1 should be less than A2).
Fixes for common time travel hiccups
- If you see ###### instead of a date in a cell, try making the column wider. If you still see ######, that means the date value is not understandable by Excel (negative numbers, dates prior to 1st of January 1900 etc.)
- Often when pasting date values in to Excel, you notice that they are not treated as dates. Use these techniques to fix.
- If you pass in-correct values or use wrong parameters, your date formulas show an error like #NUM or #VALUE. Read this to understand how to fix such errors.
Quiz time for time travelers
I see that you safely made it here. I hope you had a good journey. Let me see how good your time traveling is. Answer these questions:
- Write a formula to take date in A1 to next month’s first Monday.
- Given a date in A1, find out the closest Christmas date to it.
Building your own time machine? Check out these tips too
If you work with date & time values often, then learning about them certainly pays off. Read below articles to one up your time travel awesomeness.
- Using Date & Time in Excel
- How to calculate common holiday dates in Excel?
- How to calculate payroll dates?
- How to sort a bunch of birth dates by birthday?
- Check if two dates are in same month
Good luck time traveling. I will see you again in future 🙂
PS: Make sure you attempt the challenges and post your answers in comments.
25 Responses to “Shift Calendar Template – FREE Download”
Hi Chandoo,
your recent postings include only Excel 2007 templates. Unfortunately the company I work at still runs Excel 2003. Is it possible to get your awesome files in other excel version as well?
Thanks so much for your great excel stuff!
Is it possible to do this for shifts with hours instead of days? To organise a three shift day?
Thanks in advance,
Stelios
In my organization there are 45 employees i need split then into three shifts ex:A shift:14,B shift:14,C shift:14 and week off:3 kindly help me on this.
@Masthan
You need to understand what rules your company has for the various shifts / roster combinations
Chandoo, I once did a shift control spreadsheet for my team. I put one person in each line, the columns were the days. I put a shift code in each cell indicating in which shift that person should work, or if the person were out that day. I have two codes for being out. One is for vacations and one is to compensate days worked in weekends. This way I was able to count how many persons I have in each shift, how many were on vacations and how many were out compensating (that's the term we use here) weekend worked hours.
Later I included the possibility of a person be in two lines one for normal hours other for overtime. This is mainly used for planning purposes. If you would like I can send you an example. The only problem of this spreadsheet is that we don't have a person view, only this consolidated view.
Hi George, I would like to have a copy of your spreadsheet if you can share it.
Thanks in advance, Chuck
Hi Chandoo,
Where is the code located ? is it VBA ? If so , how do you hide it ? Or it is .NET ?
Thx
@Idan
.
No VBA or code, it is all done with Mirrors.
Only Joking,
.
But there is no VBA or code,
It is all done with Named Formulas and Lookups.
Have alook at the cells in the calander area and Named Formulas in the Formulas, Name Manager Tab.
How can i calculate between two or more different workbooks? Please, reply me as early as possible.
@Anand
Open the workbooks you want to link to
Start a formula = and click and change between workbooks as required.
You can use the View, Switch window menu to change workbooks mid formula
The format for using workbooks is
=[Workbook.xlsm]Sheet1!$A$1
or
=SUM('[Book2.xls]Sheet1'!$A$1:$D$10)
etc
Hi Chandoo,
I am working with a call centre wherein i ned to update at the month end 20 to 30 employees login hours which are defict to track it at the month end is very difficult is there any template which can be made to track that why on a particular day a guy who needs to be on calls was why not on calls.
Thank you so much Chandoo. This is really helping me. As usual, you rock.
What's FortyTwoDays and Calendar in Name manager?
Both are unused and FortyTwoDays doesn't make any sense.
I have a SQL db that contains records of events scheduled/completed on a particular date. Can this method ous building a calendar be used to display those events on the respective day?
Positively awesome!
I'm attempting to help a friend create a schedule for adult classes - and of course its not"paid help". Here is the scenario:
20 classes, instructor, room#, student class size, start date, number of class days (need to subtract weekends)
class
instructor
room
students
start
#days
PATH
karen
201
21
01/01/13
11
BILLING
jane
401
15
01/12/13
13
MEDISOFT
mike
301
11
01/25/13
9
he'd like to see these classes show up in different colors within the same month's calendar chart. He can draw it, but I'd like to see it done automatically through data, and I just can't visualize it, but I KNOW this will work - can you help?
Jan 🙂
Dear chandoo,
Try many way to download still can't access. Any way we want to try out 3 shifts with 3 guys in a group .eg Group A Morn, Group B Night and Group C Rest. And every each group must work on sunday to take turns. In fact we are security teams so that's why sunday is required to work. Pls guide and show how to put in the working calendar. Thank you in advance.
I've been trying to copy and/or recreate this to use in a workbook I'm doing for the transportation department I'm working for. I need to have the calendar on the first sheet in my document (it has graph's from data on another sheet). I'm trying to use it to track (with the conditional formatting) accidents and injuries. I've redone the conditional formatting to do 4 different accident types (no injury, near miss, OSHA recordable injury and work loss injury), but when I enter the formula's you have in the calendar portion where it says "DateOfFirst-FirstWeekDay" I can't figure out how you did that. Are you able to help?
I would like to use Excel to solve the following problem for a community work. I want to create a Driver schedule for a given month from a pool of volunteers for a community service. Each of these volunteers can drive only on specific days in a week. I would like to populate the driving schedule for each weekday with primary, secondary and tertiary drivers in a random fashion so that I do not overburden one person. I would greatly any help you can provide.
Hi chandoo,
Thanks for your valuable effort for create this template and let me know how to add multiple employees in the the Roaster.
Hi Chandoo,
This article on shift roaster is very helpful. Could you please let me know how i can use the same for n number of resources who work 24/7, considering their leaves and holidays?
Thanks,
Savitha
Hi Chandoo,
This article on shift roaster is very helpful to all. Could you please let me know how i can use the same if I want to add for some more shifts, since the color is not getting change if I add more shifts like 4,5 etc.,
Thanks,
Murali
nice post
How can I change the date to 2017 under Shift Data worksheet.
solution 1:
mydata=B2:C16
stoplist=E2:E8
=LET(RNG,A2:A16,SMR,C2:C16, F,(RNG=E2)+(RNG=E3)+(RNG=E4)+(RNG=E5)+(RNG=E6)+(RNG=E7)+(RNG=E8),SUM(SMR)-SUM(SMR*F))
=LET(RNG,A2:A16,SMR,C2:C16,RH,N(B2:B16=B2), F,(RNG=E2)+(RNG=E3)+(RNG=E4)+(RNG=E5)+(RNG=E6)+(RNG=E7)+(RNG=E8),TOT,SUM(SMR)-SUM(SMR*RH*F),SUM(SMR*RH)-SUM(SMR* RH*F))
ALTERNATE SOLUTION
=SUM(C2:C16)-SUM(FILTER(C2:C16,ISNUMBER(BYROW(A2:A16,LAMBDA(a,TOROW(SEARCH(a,E2:E8),2))))))
=SUM((B2:B16=B2)*(C2:C16))-SUM((ISNUMBER(BYROW(A2:A16,LAMBDA(a,TOROW(SEARCH(a,E2:E8),2))))*(B2:B16=B2)*(C2:C16)))
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source,null,";",Replacer.ReplaceValue,{"Column1"}),
#"Transposed Table" = Table.Transpose(#"Replaced Value"),
#"Removed Other Columns" = Table.SelectColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87"}),
#"Merged Columns" = Table.CombineColumns(#"Removed Other Columns",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
#"Added Prefix" = Table.TransformColumns(#"Split Column by Delimiter", {{"Merged", each "|" & _, type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Added Prefix","||","|",Replacer.ReplaceText,{"Merged"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value1", "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Merged.1"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
#"Removed Duplicates"