New Year's Day 2018 is on January 1, 2018 or Jan/1/18 in the US, but in much of the world it's on 1 January 2018 or 1/Jan/18. And the minute before might be 12:59 PM to one person, 23:59:59 to another. And to your computer, the time is 1514725140—the exact number of seconds since January 1, 1970 of all things.
Dates and times are confusing. Add in time zones, and you'll quickly wonder whether time's real—and if you should just go hide in a cave.
Don't despair. Computers are smart enough to figure out what date and time you're meaning—and can convert them to the format that works best for you. Here's how to format and convert dates and times in Excel, Google Sheets, and hundreds of other apps.
How to Reformat Dates and Times in Spreadsheets
Have a lot of dates and/or times that need to be switched to the same format? A spreadsheet is your best friend. Whether you're using Google Sheets, Microsoft Excel, Apple Numbers, or any other popular spreadsheet apps, the steps are typically the same.
Add your dates to your spreadsheet—and don't worry if they're all in different formats. Then select the dates, and do the following to reformat them:
- Google Sheets: Click the Format menu, select Number, then choose the date format you want. Or, click More Formats -> More date and time formats and either select a style or add the date style you need.
- Microsoft Excel: Click the Number Format drop-down from the main toolbar—typically it'll say Custom if you've selected several different date formats. Select a long (e.g., Thursday, November 25, 2017) or short date (e.g., 11/25/17), or click More Number Formats to pick another option.
- Apple Numbers: Click the Cell tab in the sidebar, then click the Number Format menu and select the style you want, or click Create Custom Format to add your own format.
Your newly converted dates look perfect in your spreadsheet—but they're actually saved internally as a much more detailed date than you likely see. The spreadsheet might just show March 12, but still stores the full date as 3/12/2015 23:04:05. That way, you can copy these formatted dates and use them in any other app. But if you ever want to go back to the original style, the old data's still there in your spreadsheet.
How to Convert Dates in WolframAlpha
Perhaps the easiest way to figure out what any date means—and what time that'd be in your timezone—is with WolframAlpha, the computational knowledge engine and the tech behind many of Siri's answers. It can figure out what most things mean and explain them in a way you understand.
Enter a UNIX timestamp, say, and it'll tell you that time in UTC (the universal time standard commonly used to regulate clocks around the world)—along with what time that'd be in your local timezone. Type in a longer date and time followed by what you want to convert it to—"11 AM january 2nd new york time in singapore," for instance—and WolframAlpha will give you the original date and time in several formats, tell you how long it is until that date, and show what time and date that would be in Singapore, New York, and your current timezone.
If you ever get a message with a weird looking date, just paste it into WolframAlpha. It'll likely figure it out and tell you what time it really means.
Automatically Convert Dates and Times from Apps with Zapier
Dates can be confusing enough in your documents, but in your work apps, it's crucial to get things right. When one app has dates in one format and another app expects them in a different format, moving data between them can get hairy. If you have integrations set up between these apps and the dates are in different formats, the integrations will quit working at best—and things will get scheduled at the wrong time at worst.
That's why app automation platform Zapier includes a date and time formatter tool. It can take almost any date and time, figure out what it means, and convert it to the format you need. Here's how:
- Log into Zapier or create a free account.
- Click the "Make a Zap" button to set up a Zap: Connect the app with your dates—perhaps a form app, sales tool, or calendar—to Zapier and select the correct trigger, what will start the workflow to copy your data to another app.
- Add a Formatter step to your Zap with the Date/Time action, and choose Format as the Transform.
- Click the
+icon beside the Input field to add the date from your first app, and choose the date and time format you want in theTo Formatfield.
Need to switch timezones? Zapier can do that, too. Just select the new timezone you want to use, then the original timezone that the old date format was using, and Zapier will convert the time while it's formatting your date.
And that's it. Test that step, and Zapier will format your date and time the way you want—and you can use your correctly formatted date in the next apps in your workflow.
It's hard enough to keep dates straight in your head, and when you're unsure what a date is on your documents and spreadsheets, you'll waste time trying to figure things out every time you open it. And if you send the wrong format of date to your apps, you might end up with something happening at the wrong time.
Don't take your chances. Use a spreadsheet to clean up your dates, a search to double-check what a date means, or a Zapier automation to automatically make sure your dates are in the correct format. You'll never have to do a double-take at your calendar again.
Do More With Zapier Formatter
Want to automate more of your work with text? Check out the other tutorials in this series for more ways to use Formatter—along with handy tips for other apps you might already be using:
- How to Split Text in Excel, Google Sheets, and Your Other Favorite Apps
- How to Extract Email Addresses, Phone Numbers, and Links From Text
- How to Automatically Capitalize Text the Way You Want
- How to Find and Replace Any Text in Your Documents
- How to Automatically Convert Markdown to HTML
source https://zapier.com/blog/format-datetimes/
No comments:
Post a Comment