Unix to Excel date conversion: three hidden pitfalls every developer should know

·3 views

Converting between Unix timestamps and Excel serial dates involves three common failure points: the epoch reference, the unit of time, and a phantom date bug dating back to 1900. Excel's date system mistakenly includes 29 February 1900 — a day that never existed — inherited from a Lotus 1-2-3 bug that Microsoft deliberately preserved to maintain backward compatibility. The standard conversion formula divides a Unix timestamp by 86,400 and adds 25,569, the Excel serial number for 1 January 1970, but developers must also account for time zone offsets since Excel stores no timezone data. Digit count matters too, as 10-digit values are seconds, 13-digit are milliseconds, and 19-digit nanosecond timestamps can cause precision loss in JavaScript due to floating-point limits. Additional edge cases include the legacy 1904 date system used in older Mac Excel files, which shifts all dates by 1,462 days when opened in standard Excel.

Read the full story at DEV Community

This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)

Mock HttpMessageHandler, Not Interfaces, for Better HttpClient Testing in .NET

A common but flawed practice among .NET developers involves wrapping HttpClient behind a custom interface to enable unit testing, which bypasses real serialization, headers, and error handling. Since HttpClient accepts an HttpMessageHandler in its constructor, developers can inject a mock handler directly to intercept and validate outgoing HTTP requests without losing built-in features. This approach allows tests to verify request methods, URIs, status codes, and response deserialization in a realistic way. Helper classes can further simplify test setup by creating pre-configured mock clients for both success and error scenarios. The technique avoids unnecessary abstractions while keeping tests accurate and maintainable.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

How to Use AI Coding Assistants to Fix Bugs Without Breaking Your Codebase

AI coding assistants can quickly resolve small software bugs, but incomplete context in prompts often leads to unintended side effects elsewhere in the code. A common pitfall occurs when an AI fixes a bug in a shared function without knowing other parts of the app depend on it, breaking seemingly unrelated features. Developers are advised to write precise bug descriptions — specifying exact conditions and expected behavior — rather than vague requests that may cause the AI to rewrite unnecessary code. Providing the AI with all relevant files and dependent functions, not just the broken snippet, helps it make targeted fixes. Asking the AI to explain its proposed changes before applying them gives developers a chance to catch overly broad edits early.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Elaine Platform Lets Students and AI Agents Build a Shared Knowledge Base

Elaine is a course community platform designed for bootcamps, academies, corporate training programs, and universities, where students and AI agents interact within shared channels. When an AI agent answers a question not yet on record, it generates a 'card' — a versioned markdown page that logs the answer, its sources, and what it supersedes. Future questions on the same topic are answered by composing from existing cards, with a visible reference indicating the knowledge is already on file. The system aims to reduce the social pressure of asking questions in a live classroom by letting students engage asynchronously, including at off-hours. Over time, collective understanding accumulates in a shared card file rather than residing solely with the instructor.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

App 'Deadweight' Calculates True Cost of Donating Goods to Disaster Zones

A developer built Deadweight, a free web tool, for a DEV Weekend Challenge to help donors understand the real logistical cost of sending physical goods to disaster-affected areas. The humanitarian sector has long warned that unsolicited donations often worsen relief efforts — a phenomenon sometimes called the 'second disaster' — with past examples including donated goods sitting unused in Vanuatu for a year after Cyclone Pam. The tool lets users build a manifest of items, select a shipping route, and receive a line-by-line USD breakdown of freight, sorting, and disposal costs against the declared value of goods. Each cost figure cites its source, date, and confidence level, and assumptions are explicitly flagged to ensure transparency. The app also suggests a counterfactual showing how the same money spent on items listed in an official flash appeal would fare under the same calculations.

0 comments Read more at DEV Community